Graph Database features in SQL Server 2019

SQL Server 2017 introduced Graph database features where we can represent the complex relationship or hierarchical data. We can explore the following articles to get familiar with the concept of the Graph database.

Graph database contains the following elements

Node: Nodes in graph database represents an entity i.e. customer, employee etc.
Edge: Edge represents the relationship between nodes

SQL Server 2019 contains below enhancements for the Graph database.

Edge Constraints (CTP 2.0)
Match support in MERGE statement
Use a derived table or view aliases in graph match query

In this article, we will view the edge constraint with SQL Server 2019 preview.

Edge Constraints (CTP 2.0) in SQL Server 2019

Typically, graph databases in SQL Server 2017 look as shown below. Here, you can see that an edge could connect any node to any other node. There were no restrictions on the edge connections. Edge is bidirectional in SQL Server 2017 i.e. edge can connect with any nodes in any direction.

Now let us consider a scenario where we want to place some restrictions on the edge that it should not be able to connect to each node. If someone tries to create an edge between those, it should generate an error.

Let us consider an example where we have two nodes:

Administrators
Users

We have an edge connecting these nodes i.e. ‘Authorization’ as shown here.

In the above example, the administrator can provide authorization to do any work to the user, but a user cannot give authorization for the administrator.

In SQL Server 2017, we create the nodes and edge table as shown below:

Create a sample database and Node table ‘administrator’:

Create database GraphDB

Use GraphDB

DROP TABLE IF EXISTS administrator

CREATE TABLE administrator (ID INTEGER PRIMARY KEY, administratorName VARCHAR(100)) AS NODE;

INSERT INTO administrator

VALUES (1,'Rajendra')

SELECT * FROM administrator

Create Node table ‘Users’ and insert data into it:

1
2
3
4
5

CREATE TABLE Users (ID INTEGER PRIMARY KEY, UserName VARCHAR(100)) AS NODE;
INSERT INTO Users
VALUES (1,'Sonu')
Go
SELECT * FROM Users

Create edge table ‘ Authorization’ and connect the nodes around it:

DROP TABLE IF EXISTS [Authorization]

CREATE TABLE [Authorization] AS EDGE

INSERT INTO [Authorization] ($from_id ,$to_id )

VALUES (

(SELECT $node_id from administrator where ID=1)

,(SELECT $node_id from Users where ID=1))

select * from [Authorization]

Now let us insert one more record into users table.

INSERT INTO Users

VALUES (2,'Mohit')

In this step, let us try to insert an edge records from users to administrator node using the below query. It successfully inserted the record.

INSERT INTO [Authorization] ($from_id ,$to_id )

VALUES (

(SELECT $node_id from Users where ID=2),

(SELECT $node_id from administrator where ID=1))

select * from [Authorization]

You can notice here that we have inserted edge record from user to administrator while it is not our desired behavior. We want to restrict this behavior but SQL Server 2017 does not provide any options to do so.

Let us move to SQL Server 2019 and see what it offers for this problem.

SQL Server 2019 Edge constraints

In SQL Server 2019, we can define edge constraint. We can define the condition in the edge constraint from which edge could not connect.

For example, in our example, above we want a constraint from administrator to the user node. If someone tries to insert records from Users to Administrator, it should fail.

In SQL Server 2019, we can define contains as shown in below query. You can notice the difference in creating edge table in SQL Server 2017 and SQL Server 2019 preview.

In below query, we defined a constraint that allows connection from Node ‘Administrator’ To Node ‘Users’.

CREATE TABLE [Authorization]

(

CONSTRAINT EC_Authorization Connection (Administrator TO Users)

)

As Edge

Now, we can connect the edge between nodes as usual way from the Node ‘Administrator’ To Node ‘Users’:

INSERT INTO [Authorization] ($from_id ,$to_id )

VALUES (

(SELECT $node_id from administrator where ID=1)

,(SELECT $node_id from Users where ID=1)

)

select * from [Authorization]

We defined constraint from Node ‘Administrator’ To Node ‘Users’ in edge table. Therefore, we will test inserting edge record from Node ‘Users’ to Node ‘Administrator’.

INSERT INTO Users

VALUES (2,'Mohit')

INSERT INTO [Authorization] ($from_id ,$to_id )

VALUES (

(SELECT $node_id from users where ID=2)

,(SELECT $node_id from administrator where ID=1)

)

It fails with below error message. You can see in the error message that since we are trying to insert edge between Users to Administrator. It violates the edge constraint we defined with edge table.

Msg 547, Level 16, State 0, Line 19

The INSERT statement conflicted with the EDGE constraint “EC_Authorization”. The conflict occurred in database “GraphDB”, table “dbo.Authorization”.

The statement has been terminated.

Now let us add one more node Groups in our example. A user who is part of the group can have the required permissions. The graph should look like below.

Create Node ‘Usergroups’ and insert a record into it.

CREATE TABLE UserGroups

(

ID INTEGER PRIMARY KEY,

[UserGroupName] NVARCHAR(100) NOT NULL,

) AS NODE

INSERT INTO UserGroups

VALUES (1,'ReadUserGroup')

Create Edge ‘permissions’

1
2
3

Create table [Permissions] as edge
Go

Previously we have defined constraints that allow inserting from Node ‘administrators’ to Node ‘users’. Therefore, if we connect edge from Node ‘usergroups’ to Node ‘users’ we get below error message.

INSERT INTO [Authorization] ($from_id ,$to_id )

VALUES (

(SELECT $node_id from usergroups where ID=1)

,(SELECT $node_id from users where ID=1)

)

We can define multiple constraints on an edge table. In below query, we have defined one more constraint to insert from Node ‘Usergroups’ to Node ‘Users’.

ALTER TABLE [Authorization] ADD CONSTRAINT EC_Connection1_Authorization Connection

(

Administrator TO Users,

UserGroups TO Users

)

We can delete the old constraint since, in the above query, new constraint contains both the nodes.

1 2	ALTER TABLE [Authorization] DROP CONSTRAINT EC_Authorization GO

INSERT INTO Users

VALUES (3,'Akshita')

Now let us test few scenarios to insert into edge table.

Insert into edge table from Node ‘Users’ to Node ‘administrator’: It gives an error message due to the defined constraint.

1
2
3
4
5

INSERT INTO [Authorization] ($from_id ,$to_id )
VALUES (
(SELECT $node_id from users where ID=2)
,(SELECT $node_id from administrator where ID=1)
)
Insert into edge table from Node ‘usergroups’ to Node ‘users’: It successfully inserts record as we have defined constraint to allow this.

1
2
3
4
5

INSERT INTO [Authorization] ($from_id ,$to_id )
VALUES (
(SELECT $node_id from UserGroups where ID=1)
, (SELECT $node_id from users where ID=1)
)
Insert into edge table from Node ‘users’ to Node ‘UserGroups’: We cannot insert record here due to the constraint does not allow going towards Node ‘users’ to Node ‘UserGroups’

1
2
3
4
5
6

INSERT INTO [Authorization] ($from_id ,$to_id )
VALUES (
(SELECT $node_id from users where ID=1),
(SELECT $node_id from UserGroups where ID=1)

)

Delete records in a graph database with SQL Server 2019

In SQL Server 2017, we can delete any records from the graph database whether that contains the connecting edge into the graph or not. This imbalances the graph database since the edge is having no connections for the record. We cannot ensure data integrity in this case.

Let us delete a record in SQL Server 2017 from the table and records created earlier.

However, we can see that connecting edge already exist which should not be case ideally.

Now let us observe the same behavior with SQL Server 2019. You can see that delete statement fails with the error

Msg 547, Level 16, State 0, Line 3

The DELETE statement conflicted with the EDGE REFERENCE constraint “EC_Authorization”. The conflict occurred in database “GraphDB”, table “dbo.Authorization”.

The statement has been terminated.

System catalog views in SQL Server 2019 for edge constraints

SQL Server 2019 contains new system catalog view to get information about the defined edge constraints for SQL Server 2019.

sys.edge_constraints: This system view show details about the edge constraint
sys.edge_constraint_clauses: this system view shows details about clause of an edge constraint

We can join these system views to get complete information from both of these.

SELECT

EC.name AS edge_constraint_name

, OBJECT_NAME(EC.parent_object_id) AS [edge table]

, OBJECT_NAME(ECC.from_object_id) AS [From Node table]

, OBJECT_NAME(ECC.to_object_id) AS [To Node table],

EC.type_desc,

EC.create_date

FROM sys.edge_constraints EC

INNER JOIN sys.edge_constraint_clauses ECC

ON EC.object_id = ECC.object_id

We can filter out the records by specifying where condition. For example, below I have filtered out the result for the Node table administrators.

SELECT

EC.name AS edge_constraint_name

, OBJECT_NAME(EC.parent_object_id) AS [edge table]

, OBJECT_NAME(ECC.from_object_id) AS [From Node table]

, OBJECT_NAME(ECC.to_object_id) AS [To Node table],

EC.type_desc,

EC.create_date

FROM sys.edge_constraints EC

INNER JOIN sys.edge_constraint_clauses ECC

ON EC.object_id = ECC.object_id

WHERE ECC.from_object_id = object_id('administrator')

Conclusion

Edge constraints in SQL Server 2019 are helpful to maintain data integrity for a Graph database. We can now specify the constraints on edge table to control the direction of flow for the edge.

Author
Recent Posts

Rajendra Gupta

Hi! I am Rajendra Gupta, Database Specialist and Architect, helping organizations implement Microsoft SQL Server, Azure, Couchbase, AWS solutions fast and efficiently, fix related issues, and Performance Tuning with over 14 years of experience.

I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.

I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.

Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.

Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta

Latest posts by Rajendra Gupta (see all)

Understanding PostgreSQL SUBSTRING function - September 21, 2024
How to install PostgreSQL on Ubuntu - July 13, 2023
How to use the CROSSTAB function in PostgreSQL - February 17, 2023

Edge Constraints (CTP 2.0) in SQL Server 2019

SQL Server 2019 Edge constraints

Delete records in a graph database with SQL Server 2019

System catalog views in SQL Server 2019 for edge constraints

Conclusion

Related posts: