The Issue\Purpose of Multiple Edge Constraint/Conditions

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:

CREATE TABLE dbo.Node1(Name varchar(20)) AS NODE;
CREATE TABLE dbo.Node2(Name varchar(20)) AS NODE;

INSERT INTO  dbo.Node1(Name)  VALUES ('One'),('Two');
INSERT INTO  dbo.Node2(Name)  VALUES ('Buckle'),('Shoe');

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:

CREATE TABLE dbo.Edge1
(
        CONSTRAINT EC_Edge1_1 CONNECTION (dbo.Node1 TO dbo.Node1,
                                          dbo.Node2 TO dbo.Node2) 
                             ON DELETE NO ACTION,
        CONSTRAINT EC_Edge1_2 CONNECTION (dbo.Node2 TO dbo.Node1) 
                             ON DELETE CASCADE
) AS EDGE;

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

--from node1 to node1 fails:
INSERT INTO dbo.Edge1($from_id, $to_id)
SELECT (SELECT $node_id FROM dbo.Node1 WHERE name = 'One'),
       (SELECT $node_id FROM dbo.Node1 WHERE name = 'Two');

Results in this:

Msg 547, Level 16, State 0, Line 30
The INSERT statement conflicted with the EDGE constraint "EC_Edge1_2". 
The conflict occurred in database "tempdb", table "dbo.Edge1".

And this:

INSERT INTO dbo.Edge1($from_id, $to_id)
SELECT (SELECT $node_id FROM dbo.Node2 WHERE name = 'Buckle'),
       (SELECT $node_id FROM dbo.Node1 WHERE name = 'One');

Violates the other constraint:

Msg 547, Level 16, State 0, Line 40
The INSERT statement conflicted with the EDGE constraint "EC_Edge1_1". 
The conflict occurred in database "tempdb", table "dbo.Edge1".

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:

DROP TABLE dbo.Edge1;
CREATE TABLE dbo.Edge1
(
        CONSTRAINT EC_Edge1 CONNECTION (dbo.Node1 TO dbo.Node1)                           
                                  ON DELETE NO ACTION,
) AS EDGE;

Now you can insert:

INSERT INTO dbo.Edge1($from_id, $to_id)
SELECT (SELECT $node_id FROM dbo.Node1 WHERE name = 'One'),
       (SELECT $node_id FROM dbo.Node1 WHERE name = 'Two');

But still not:

INSERT INTO dbo.Edge1($from_id, $to_id)
SELECT (SELECT $node_id FROM dbo.Node2 WHERE name = 'Buckle'),
       (SELECT $node_id FROM dbo.Node1 WHERE name = 'One');

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

Msg 547, Level 16, State 0, Line 70
The INSERT statement conflicted with the EDGE constraint "EC_Edge1". 
The conflict occurred in database "tempdb", table "dbo.Edge1".

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

ALTER TABLE dbo.Edge1
  ADD CONSTRAINT EC_Edge1_NEW CONNECTION 
                                  (dbo.Node1 TO dbo.Node1,
                                   dbo.Node2 TO dbo.Node1) 
                            ON DELETE NO ACTION;

This still won’t work:

INSERT INTO dbo.Edge1($from_id, $to_id)
SELECT (SELECT $node_id FROM dbo.Node2 WHERE name = 'Buckle'),
       (SELECT $node_id FROM dbo.Node1 WHERE name = 'One');

Until you drop the original edge constraint:

ALTER TABLE dbo.Edge1 DROP CONSTRAINT EC_Edge1;

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

EXEC sp_rename 'EC_Edge1_NEW','EC_Edge1';

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.

The post The Issue\Purpose of Multiple Edge Constraint/Conditions appeared first on Simple Talk.

from Simple Talk https://ift.tt/3cAAPQ5
via