Rajendra Gupta

Graph Database features in SQL Server 2019 – Part 1

November 21, 2018 by

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.

  1. Edge Constraints (CTP 2.0)
  2. Match support in MERGE statement
  3. 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 Node table ‘Users’ and insert data into it:


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


Now let us insert one more record into users table.

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.


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’.


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


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’.


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 Edge ‘permissions’


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.


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’.


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


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.

  • Insert into edge table from Node ‘usergroups’ to Node ‘users’: It successfully inserts record as we have defined constraint to allow this.


  • 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’


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.

  1. sys.edge_constraints: This system view show details about the edge constraint
  2. 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.


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


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.


Rajendra Gupta

Rajendra Gupta

Rajendra has 8+ years of experience in database administration having a passion for database performance optimization, monitoring, and high availability and disaster recovery technologies, learning new things, new features.

While working as a Senior consultant DBA for big customers and having certified with MCSA SQL 2012, he likes to share knowledge on various blogs.
He can be reached at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta
Rajendra Gupta

Latest posts by Rajendra Gupta (see all)

2,427 Views