Edge Constraints: Controlling Which Nodes a Graph Edge Connects

Edge constraints tell SQL Server which node tables an edge may connect. Without one, a graph edge table happily links a person to a product. With one, that edge is refused with error 547. Let me show both.

What an edge table allows by default

Picture a loader job that fills a “works with” edge table. One night it reads a staging file with a bad column. Now an edge says a person works with a product. Nothing fails. The graph just fills with nonsense, and the queries you build on it return nonsense too.

The demo below makes three node tables, Person, Company and Product, with one row each. It also makes an edge table with no rules, and connects the person to the product. The insert succeeds.

DROP TABLE IF EXISTS dbo.GraphLooseEdge;
DROP TABLE IF EXISTS dbo.GraphWorksWith;
DROP TABLE IF EXISTS dbo.GraphPerson;
DROP TABLE IF EXISTS dbo.GraphCompany;
DROP TABLE IF EXISTS dbo.GraphProduct;

CREATE TABLE dbo.GraphPerson  (Id int PRIMARY KEY) AS NODE;
CREATE TABLE dbo.GraphCompany (Id int PRIMARY KEY) AS NODE;
CREATE TABLE dbo.GraphProduct (Id int PRIMARY KEY) AS NODE;
INSERT dbo.GraphPerson  VALUES (1);
INSERT dbo.GraphCompany VALUES (1);
INSERT dbo.GraphProduct VALUES (1);

CREATE TABLE dbo.GraphLooseEdge AS EDGE;
INSERT dbo.GraphLooseEdge ($from_id, $to_id)
SELECT p.$node_id, x.$node_id
FROM dbo.GraphPerson AS p
CROSS JOIN dbo.GraphProduct AS x;

SELECT COUNT(*) AS LooseEdges FROM dbo.GraphLooseEdge;

LooseEdges is 1. The edge table accepted a person-to-product link because nobody told it not to.

Add the edge constraint

Now make the real edge table. The CONNECTION clause lists the allowed pair, here from a person to a company. ON DELETE CASCADE says that when a company is deleted, its edges go with it. Then add the one valid edge.

CREATE TABLE dbo.GraphWorksWith
(
    CONSTRAINT EC_GraphWorksWith
        CONNECTION (dbo.GraphPerson TO dbo.GraphCompany) ON DELETE CASCADE
) AS EDGE;

INSERT dbo.GraphWorksWith ($from_id, $to_id)
SELECT p.$node_id, c.$node_id
FROM dbo.GraphPerson AS p
CROSS JOIN dbo.GraphCompany AS c;

To review constraints later, join sys.edge_constraints to sys.edge_constraint_clauses. This shows each edge table, its allowed pair, and its delete action in one place.

SELECT ec.name AS ConstraintName,
       OBJECT_NAME(ec.parent_object_id) AS EdgeTable,
       OBJECT_NAME(cl.from_object_id)   AS FromTable,
       OBJECT_NAME(cl.to_object_id)     AS ToTable,
       ec.delete_referential_action_desc AS DeleteAction
FROM sys.edge_constraints AS ec
JOIN sys.edge_constraint_clauses AS cl ON cl.object_id = ec.object_id;

Try the wrong connection, then delete the company

Now repeat the bad insert, this time into the constrained table. It fails with error 547, and PRINT shows the message with the constraint name. Then count the edges, delete the company, and count again.

BEGIN TRY
    INSERT dbo.GraphWorksWith ($from_id, $to_id)
    SELECT p.$node_id, x.$node_id
    FROM dbo.GraphPerson AS p
    CROSS JOIN dbo.GraphProduct AS x;
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber;
    PRINT ERROR_MESSAGE();
END CATCH;

SELECT COUNT(*) AS BeforeDelete FROM dbo.GraphWorksWith;
DELETE dbo.GraphCompany WHERE Id = 1;
SELECT COUNT(*) AS AfterDelete FROM dbo.GraphWorksWith;

The edge count goes from 1 to 0. Nobody deleted the edge. The cascade did it. Check that this matches your business rule before you use it, because losing edges silently can surprise people. The cascade never deletes the person.

Back to our loader job. With the constraint in place, the bad night ends differently. The insert fails, the job stops, and someone looks at the staging file in the morning instead of weeks later. Error 547 is also the number foreign keys use, so your existing error handling for bad keys probably catches it already.

What an edge constraint does not do

An edge constraint checks the types at both ends and nothing else. It does not limit a person to one company. To show that, add two companies and connect the same person to both. Both inserts succeed.

INSERT dbo.GraphCompany VALUES (2), (3);

INSERT dbo.GraphWorksWith ($from_id, $to_id)
SELECT p.$node_id, c.$node_id
FROM dbo.GraphPerson AS p
CROSS JOIN dbo.GraphCompany AS c;

SELECT COUNT(*) AS CompaniesForPerson1 FROM dbo.GraphWorksWith;

If a person may work with only one company, you need another rule, such as a unique index on the edge. Rules about properties, like an active flag or a department, also need their own checks. Test both kinds of rules with bad rows before you call a graph protected.

Last, clean up. Drop the edge tables first, then the node tables.

DROP TABLE IF EXISTS dbo.GraphLooseEdge;
DROP TABLE IF EXISTS dbo.GraphWorksWith;
DROP TABLE IF EXISTS dbo.GraphPerson;
DROP TABLE IF EXISTS dbo.GraphCompany;
DROP TABLE IF EXISTS dbo.GraphProduct;

Decide which endpoints belong together before the first edge is loaded.

A graph edge is not any link you can store, it is a relationship with allowed ends.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.