This article is a continuation to the earlier wiki article Graph Tables in SQL 2017.

In the preceding article, the concept of Graph Tables was introduced along with use cases.
This article explains the concept of enforcing Edge constraints within the Graph tables and their advantages   

Creating Graph Tables  

As seen from the previous article, Graph tables can be created using CREATE TABLE syntax.  

For the sake of this article, the earlier Graph tables shall be considered as per below

Taking the above example the Graph table creation script would look like below 

CREATETABLE [dbo].[ForumMembers](
    [MemberID] [int] IDENTITY(1,1) NOTNULL,
    [MemberName] [varchar](100) NULL
CREATETABLE [dbo].[ForumPosts](
    [PostID] [int] NULL,
    [PostTitle] [varchar](100) NULL,
    [PostBody] [varchar](1000) NULL,
    [OwnerID] [int] NULL
CREATETABLE [dbo].[Likes]
CREATETABLE [dbo].[Replies]

And the data population script would be as below 

INSERT ForumMembers values('Peter'),('ABC'),('PQR'),('George'),('Mary'),('XYZ')
INSERTINTO [dbo].[ForumPosts](
    (1,'Intro','Hi There This is ABC',2),
    (2,'Intro','Hello I''m PQR',3),
    (3,'Re: Intro','Hey PQR This is XYZ',6),
    (4,'Geography','Im George from USA',4),
    (5,'Re:Geography','I''m Mary from OZ',5),
    (6,'Re:Geography','I''m Peter from UK',1),
    (7,'Intro','I''m Peter from UK',1),
    (8,'Intro','nice to see all here!',1)
((SELECT $node_id FROM dbo.ForumPosts WHERE PostID = 4),(SELECT $node_id FROM dbo.ForumPosts WHERE PostID = 6)),
((SELECT $node_id FROM dbo.ForumPosts WHERE PostID = 1),(SELECT $node_id FROM dbo.ForumPosts WHERE PostID = 7)),
((SELECT $node_id FROM dbo.ForumPosts WHERE PostID = 1),(SELECT $node_id FROM dbo.ForumPosts WHERE PostID = 8)),
((SELECT $node_id FROM dbo.ForumPosts WHERE PostID = 1),(SELECT $node_id FROM dbo.ForumPosts WHERE PostID = 2)),
((SELECT $node_id FROM dbo.ForumPosts WHERE PostID = 4),(SELECT $node_id FROM dbo.ForumPosts WHERE PostID = 5)),
((SELECT $node_id FROM dbo.ForumPosts WHERE PostID = 2),(SELECT $node_id FROM dbo.ForumPosts WHERE PostID = 3))
((SELECT $node_id FROM dbo.ForumPosts WHERE PostID = 4),(SELECT $node_id FROM dbo.ForumMembers WHERE MemberID = 1))

Analyzing the code above, it can be seen that there’s nothing present technically which enforces any kind of relationship or direction on how the Node table rows are linked through an Edge table. Hence it is technically possible to insert a row within Edge table which breaks the logical relationship between the Node tables involved. 
So its practically possible to have a relationship like this for example 

((SELECT $node_id FROM dbo.ForumPosts WHERE PostID = 5),( SELECT $node_id FROM dbo.ForumPosts WHERE PostID = 4))

 But the relationship represented above is invalid logically as its practically not possible for a post to like another post 

Edge Constraints  

With the advent of later release versions of SQL 2017, the concept of Edge Constraints were introduced which helps to enforce the direction of relationship between two or more node tables. 

The edge constraints indicated from which node to which node an edge table relationship is supposed to connect. 

An edge constraint can be created on an existing edge table using ALTER TABLE ..ADD CONSTRAINT syntax.   

Another way to create the constraint is to define it inline along with table creation inside the CREATE TABLE statement. 

The Edge Constraint will have two parts 

  1. The name of the constraint itself 

  1. The node tables along with the direction in which edge relationship points to i.e like NodeTable1 TO NodeTable2 

As an example, a constraint created on Likes and Replies edge tables would look like the below  

ALTER TABLE [dbo].[Likes]ADD CONSTRAINT EC_Likes_MemberLikesPost CONNECTION (ForumPosts TO ForumMembers)
ALTER TABLE Replies ADD CONSTRAINT EC_Replies_PostRepliedPost CONNECTION (ForumPosts TO ForumPosts);

 This constraint in Likes table enforces the relationship to point from ForumPosts to ForumMembers node table indicating that a post will be liked by a member 
 Similarly the constraint in Replies table indicates a reply relationship between two ForumPosts i.e which post represents the reply of another post 

If you want the create the constraint inline during creation of the edge table, you need to do like this  


Once this constraint is in place, any attempts to insert a row with an invalid relationship will end in an error like below 

Msg 547, Level 16, State 0, Line 60
The INSERT statement conflicted with the EDGE constraint "EC_Likes_MemberLikesPost". The conflict occurred in database "testdb", table "dbo.Likes".
The statement has been terminated

Metadata Information 

The metadata information on edge constraints are stored in the system catalog objects  

sys.edge_constraints and sys.edge_constraint_clauses 

The sys.edge_constraints table will have the below information


Similarly sys.edge_constraint_clauses will have the below details


Here from and to object ids represent the object ids for the from and to node tables which are related using the edge table.
In the above example ForumPosts is the from and ForumMembers is the to node table. 
To get table names use OBJECT_NAME function over the ids as shown

Additional System Functions  

There are few system functions available to extract the object_id and node/edge ids from the pseudo columns within the node and edge tables. This makes it easier for users to extract the required information from these pseudo columns and use them in filter conditions within queries 

OBJECT_ID_FROM_NODE_ID – This function is used to extract object id from the $node_id pseudo column of the node table 

GRAPH_ID_FROM_NODE_ID – This function is used to extract graph id (unique identity value) from within the $node_id column of the node table 

An illustration for the above two functions can be given as below using the earlier created node table

Here the value 789577851 represents object id of the node table ForumPosts from sys.tables table and graph id value represents the individual id values for each row as generated within the $node_id column 

The object id can be used to confirm the details from sys.tables view as well 


The is_node column has value as 1 indicating that this is a node table.  

Similarly, for edge tables the below functions are available 

OBJECT_ID_FROM_EDGE_ID – This function is used to extract object id from the $edge_id pseudo column of the edge table 

GRAPH_ID_FROM_EDGE_ID – This function is used to extract graph id (unique identity value) from within the $edge_id column of the edge table 

Illustration can be given as below

As before the object details can be verified from sys.tables view


The is_edge column has value of 1 indicating that Likes is an edge table  

Additionally, there are two more functions available to generate $node_id and $edge_id values based on an object_id and an identity value. The functions NODE_ID_FROM_PARTS and EDGE_ID_FROM_PARTS can be used to generate pseudo column values in node and edge tables respectively as shown 


The above example shows way to reverse engineer the $node_id value using object_id and graph id of the row using the functions explained earlier 

Similarly for edge table 



As seen from the above illustrations Edge constraints can be defined on edge tables to enforce direction of relationships between Node tables. This article also introduces through illustration how the available system functions can be used to extract information from Node and Edge tables 

See Also