Graph Edge Constraints and a Crystal Ball

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))

 

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

 

Next step: Lottery numbers. Who knows?

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

• Public Preview of Graph Edge Constraints in SQL Server 2019
• Graph Edge Constraints

 

The post Graph Edge Constraints and a Crystal Ball appeared first on Simple Talk.

from Simple Talk https://ift.tt/2rrSh1V
via