The Issue\Purpose of Multiple Edge Constraint/Conditions

Comments 0

Share to social media

Edge constraints were added in SQL Server 2019 to make the node to edge relationship stricter/enforced, and more like typical foreign key constraints. When used, they define what node types can be used in the from and to position of the edge. What makes edges different than a many-to-many relationship in a relational table is that an edge can implement more than one many-to-many relationship in a single table. To constrain the types of data that can be put into the edge, you can use an edge constraint.

Edge constraints are very similar to implementing foreign key constraints, but there are a few key differences. Foreign keys are between two tables. Edges are between one edge table, and multiple pairs of node tables. In both cases, you can have multiple constraints, even from the same table to the same related table on the same column. However, with edge constraints, because you can have multiple pairs of expressions, and even multiple constraints, it bears discussion. If you have more than one constraint, it has one big negative, but it is allowed to implement one big positive!

Take for example, the following two nodes:

When I built my first edge with an edge constraint, I thought, what if I want the constraint do cascading deletes for one relationship but prevent deletes in others. So, I built this:

Much like you would do with a foreign key constraint. But when you try to insert any data…

Results in this:

And this:

Violates the other constraint:

Microsoft docs states:

  • If multiple edge constraints are created on a single edge table, edges must satisfy ALL constraints to be allowed.

So you cannot actually do this and get an additive configuration like a foreign key constraint. Rather, the value of allowing multiple constraints is designed to be when adding a new condition. Say your edge was:

Now you can insert:

But still not:

Which causes this error that still conflicts with the EC_Edge1 constraint:

However, to add in the new, Node2 to Node1 rows, you execute:

This still won’t work:

Until you drop the original edge constraint:

And the INSERT will now work. You can rename the constraint and all is well:

At first glance this feels very silly. Why not drop the constraint and add the new one? The value lies in the fact that when you added EC_Edge1_NEW with a new condition, because there already was an existing, trusted constraint, the Node1 to Node1 condition need not be rechecked, potentially saving quite a bit of processing time when adding a new constraint.  This is the big positive, and as long as you understand that 2 constraints are not additive, is a great thing for your administrative tasks on larger objects.