Graph Table Enhancements : Edge Constraints, System Utility Functions
Introduction
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
CREATE TABLE [dbo].[ForumMembers](
[MemberID] [int] IDENTITY(1,1) NOT NULL,
[MemberName] [varchar](100) NULL
) AS NODE
GO
CREATE TABLE [dbo].[ForumPosts](
[PostID] [int] NULL,
[PostTitle] [varchar](100) NULL,
[PostBody] [varchar](1000) NULL,
[OwnerID] [int] NULL
) AS NODE
GO
CREATE TABLE [dbo].[Likes]
AS EDGE
GO
CREATE TABLE [dbo].[Replies]
AS EDGE
GO
And the data population script would be as below
INSERT ForumMembers values('Peter'),('ABC'),('PQR'),('George'),('Mary'),('XYZ')
GO
INSERT INTO [dbo].[ForumPosts](
[PostID],[PostTitle],[PostBody],OwnerID
)
VALUES
(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)
GO
INSERT Replies VALUES
((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))
GO
INSERT Likes VALUES
((SELECT $node_id FROM dbo.ForumPosts WHERE PostID = 4),(SELECT $node_id FROM dbo.ForumMembers WHERE MemberID = 1))
GO
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
INSERT Likes VALUES
((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
- The name of the constraint itself
- 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)
GO
ALTER TABLE Replies ADD CONSTRAINT EC_Replies_PostRepliedPost CONNECTION (ForumPosts TO ForumPosts);
GO
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
CREATE TABLE Likes
(
CONSTRAINT EC_BOUGHT CONNECTION (Customer TO Product)
)
AS EDGE;
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
Conclusion
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