Graph Edge Constraints and a Crystal Ball

Comments 0

Share to social media

When I read the list of new features in SQL Server 2019 I became very proud of my crystal ball powers. In July 2017 I published an article about Graph Database feature in SQL Server 2017. In this article, besides showing the improvements and benefits I also highlighted one problem: the lack of graph edge constraints.

That’s exactly one of the new features in SQL Server 2019, edge constraints for graph databases.

On the article, I gave an example of three problems:

  • No Referential integrity
  • No control over which relations the edges can accept
  • No control of unicity of the relation (on the example, one forum post could be replying to many others – this should not be accepted).

The new constraints are able to solve two of the three problems. Let’s see an example on the edge “Likes” used in the article from last year. Forum members can like other forum members and can like posts on the forum, so, two different connections are possible on the same edge.

A single constraint can control many different kinds of relations, like on this situation, controlling the relation between members and members and members and forum posts. The T-SQL to create the constraint will be this:

ALTER TABLE Likes ADD CONSTRAINT validLikes CONNECTION
(
     ForumMembers TO ForumMembers,
     ForumMembers TO ForumPosts
)GO

Two problems are solved. First, different connections will not be accepted on this edge. For example, a post can’t like another post. This exact example in the article is now blocked by the constraint:

INSERT Likes ($to_id,$from_id) VALUES
   ((SELECT $node_id FROM dbo.ForumPosts WHERE PostID = 8),
      (SELECT $node_id FROM dbo.ForumPosts WHERE PostID = 7))

 

graph edge constraints blocking insert

The constraint also creates a referential integrity, a forum member with like can’t be deleted and leave orphan likes behind. The following statement will also be blocked by the constraint:

delete forummembers where memberid=1

 

grah edge constraints blocking delete

Next step: Lottery numbers. Who knows?

You can find more about the new constraints on these links:

 

About the author

Dennes Torres

See Profile

Dennes Torres is a Data Platform MVP and Software Architect living in Malta who loves SQL Server and software development and has more than 20 years of experience. Dennes can improve Data Platform Architectures and transform data in knowledge. He moved to Malta after more than 10 years leading devSQL PASS Chapter in Rio de Janeiro and now is a member of the leadership team of MMDPUG PASS Chapter in Malta organizing meetings, events, and webcasts about SQL Server. He is an MCT, MCSE in Data Platforms and BI, with more titles in software development. You can get in touch on his blog https://dennestorres.com or at his work https://dtowersoftware.com

Dennes's contributions