Edge constraints
Applies to:
SQL Server 2019 (15.x) and later
Azure SQL Database
Azure SQL Managed Instance
Edge constraints can be used to enforce data integrity and specific semantics on the edge tables in SQL Server graph database.
Edge Constraints
By default, edge tables don't enforce anything for the endpoints of the edge. That is, an edge in a graph database could connect any node to any other node, regardless of the type.
SQL Graph supports edge constraints, which enable users to add constraints to their edge tables, thereby enforcing specific semantics and also maintaining data integrity. When a new edge is added to an edge table with edge constraints, the Database Engine enforces that the nodes that the edge is trying to connect, exist in the proper node tables. It's also ensured that a node can't be dropped, if any edge is referencing that node.
Edge Constraint Clauses
A single edge constraint consists of one or more edge constraint clause(s).
CONSTRAINT constraint_name CONNECTION (cause1[, clause2...])
- An edge constraint clause is a pair of node table names, separated by the
TO
keyword. - The first table name in the edge constraint clause is the name of the FROM node table for the edge relationship.
- The second table name in the edge constraint clause is the name of the TO node table for the edge relationship.
- The pair of table names therefore indicates the direction of the edge relationship.
- As stated previously, an edge constraint can contain one or more edge constraint clauses.
Multiple constraints and clauses
- Multiple edge constraints, defined for the same edge table, are enforced with an
AND
operator. - Multiple edge constraint clauses, defined within the same edge constraint, are enforced with an
OR
operator.
Consider the Supplier
and Customer
nodes in your graph. Each can be related to the Product
node by a single, shared edge table: bought
. The bought
edge table supports Customer-(bought)->Product
and Supplier-(bought)->Product
relationship types. This can be accomplished using a single edge constraint with multiple edge constraint clauses.
Examples
CONSTRAINT EC_BOUGHT CONNECTION (Customer TO Product)
The above example shows one edge constraint, with one edge constraint clause. This constraint supports Customer-(bought)->Product
. That is, inserting a bought
edge relationship going from a Customer
to Product
would be allowed. If you try to insert any other combination of nodes, like Supplier-(bought)->Product
, even though it may describe a valid relationship in the real world, would fail.
CONSTRAINT EC_BOUGHT CONNECTION (Supplier TO Product, Customer TO Product)
The above example defines one edge constraint with two edge constraint clauses. These constraint clauses allow the bought
edge to contain either Supplier-(bought)->Product
or Customer-(bought)->Product
relationships. Inserting any other types of edge relationships into the bought
table would fail.
CONSTRAINT EC_BOUGHT1 CONNECTION (Supplier TO Product)
CONSTRAINT EC_BOUGHT2 CONNECTION (Customer TO Product)
The above example shows two constraints on the same edge table, with each edge constraint specifying one constraint clause. In this situation, SQL would only allow inserts that satisfy BOTH edge constraint clauses simultaneously. This isn't possible. There's no node pair that can satisfy both edge constraint clauses. This edge constraint combination makes the edge table unusable.
For a detailed explanation of where multiple edge constraints can be used in a real-life scenario, see the example "Creating a new edge constraint on existing edge table, with new edge constraint clause" later in this page.
Indexes on edge constraints
Creating an edge constraint doesn't automatically create a corresponding index on $from_id
and $to_id
columns in the edge table. Manually creating an index on a $from_id
, $to_id
pair is recommended if you have point lookup queries or OLTP workload.
ON DELETE referential actions on edge constraints
Cascading actions on an edge constraint let users define the actions that the database engine takes when a user deletes the node(s), which the given edge connects. The following referential actions can be defined: NO ACTION The database engine raises an error when you try to delete a node that has connecting edge(s).
CASCADE When a node is deleted from the database, connecting edge(s) are deleted.
Work with edge constraints
You can define an edge constraint in SQL Server by using Transact-SQL. An edge constraint can be defined on a graph edge table only. To create, delete, or modify an edge constraint, you must have ALTER permission on the table.
Create edge constraints
The following examples show you how to create an edge constraint on new or existing tables.
Create an edge constraint on a new edge table
The following example creates an edge constraint on the bought
edge table.
-- CREATE node and edge tables
CREATE TABLE Customer
(
ID INTEGER PRIMARY KEY
,CustomerName VARCHAR(100)
)
AS NODE;
GO
CREATE TABLE Product
(
ID INTEGER PRIMARY KEY
,ProductName VARCHAR(100)
)
AS NODE;
GO
CREATE TABLE bought
(
PurchaseCount INT
,CONSTRAINT EC_BOUGHT CONNECTION (Customer TO Product) ON DELETE NO ACTION
)
AS EDGE;
Define referential actions on a new edge table
The following example creates an edge constraint on the bought
edge table and defines ON DELETE CASCADE referential action.
-- CREATE node and edge tables
CREATE TABLE Customer
(
ID INTEGER PRIMARY KEY
,CustomerName VARCHAR(100)
)
AS NODE;
GO
CREATE TABLE Product
(
ID INTEGER PRIMARY KEY
,ProductName VARCHAR(100)
)
AS NODE;
GO
CREATE TABLE bought
(
PurchaseCount INT
,CONSTRAINT EC_BOUGHT CONNECTION (Customer TO Product) ON DELETE CASCADE
)
AS EDGE;
Add edge constraint to an existing edge table
The following example uses ALTER TABLE to add an edge constraint to the bought
edge table.
-- CREATE node and edge tables
CREATE TABLE Customer
(
ID INTEGER PRIMARY KEY
, CustomerName VARCHAR(100)
)
AS NODE;
CREATE TABLE Product
(
ID INTEGER PRIMARY KEY
, ProductName VARCHAR(100)
)
AS NODE;
GO
CREATE TABLE bought
(
PurchaseCount INT
)
AS EDGE;
GO
ALTER TABLE bought ADD CONSTRAINT EC_BOUGHT1 CONNECTION (Customer TO Product);
Create a new edge constraint on existing edge table, with additional edge constraint clauses
The following example uses the ALTER TABLE
command to add a new edge constraint with additional edge constraint clauses on the bought
edge table.
-- CREATE node and edge tables
CREATE TABLE Customer
(
ID INTEGER PRIMARY KEY
, CustomerName VARCHAR(100)
)
AS NODE;
GO
CREATE TABLE Supplier
(
ID INTEGER PRIMARY KEY
, SupplierName VARCHAR(100)
)
AS NODE;
GO
CREATE TABLE Product
(
ID INTEGER PRIMARY KEY
, ProductName VARCHAR(100)
)
AS NODE;
GO
CREATE TABLE bought
(
PurchaseCount INT
, CONSTRAINT EC_BOUGHT CONNECTION (Customer TO Product)
)
AS EDGE;
-- Drop the existing edge constraint first and then create a new one.
ALTER TABLE bought DROP CONSTRAINT EC_BOUGHT;
GO
-- User ALTER TABLE to create a new edge constraint.
ALTER TABLE bought ADD CONSTRAINT EC_BOUGHT1 CONNECTION (Customer TO Product, Supplier TO Product);
In the preceding example, there are two edge constraint clauses in the EC_BOUGHT1
constraint, one that connects Customer
to Product
and other connects Supplier
to Product
. Both these clauses are applied in disjunction. That is, a given edge has to satisfy either of these two clauses to be allowed in the edge table.
Create a new edge constraint on existing edge table, with new edge constraint clause
The following example uses the ALTER TABLE
command to add a new edge constraint with a new edge constraint clause on the bought
edge table.
-- CREATE node and edge tables
CREATE TABLE Customer
(
ID INTEGER PRIMARY KEY
, CustomerName VARCHAR(100)
)
AS NODE;
GO
CREATE TABLE Supplier
(
ID INTEGER PRIMARY KEY
, SupplierName VARCHAR(100)
)
AS NODE;
GO
CREATE TABLE Product
(
ID INTEGER PRIMARY KEY
, ProductName VARCHAR(100)
)
AS NODE;
GO
CREATE TABLE bought
(
PurchaseCount INT,
CONSTRAINT EC_BOUGHT CONNECTION (Customer TO Product)
)
AS EDGE;
GO
In the preceding example, imagine that we now need to also include the Supplier
to Product
relationship, through the bought
edge table. You can try adding a new edge constraint:
ALTER TABLE bought ADD CONSTRAINT EC_BOUGHT1 CONNECTION (Supplier TO Product);
However, adding a new edge constraint isn't the correct solution. We created two separate edge constraints on the bought
edge table, EC_BOUGHT
and EC_BOUGHT1
. Both these edge constraints have different edge constraint clauses. If an edge table has more than one edge constraint on it, a given edge has to satisfy ALL edge constraints to be allowed in the edge table. Since no edge can satisfy both EC_BOUGHT
and EC_BOUGHT1
here, the above ALTER TABLE
statement fails if there are any rows at all in the bought
edge table.
For this edge constraint to be created successfully, the prescribed way is to follow a sequence as shown in this sample:
-- First, add the desired ("super-set") constraint:
ALTER TABLE bought ADD CONSTRAINT EC_BOUGHT_NEW CONNECTION (Customer TO Product, Supplier TO Product);
GO
-- Then, drop the older edge constraint:
ALTER TABLE bought DROP CONSTRAINT EC_BOUGHT;
GO
-- If needed, you can rename the new edge constraint to match the original name:
EXECUTE sp_rename '[dbo].[EC_BOUGHT_NEW]', '[dbo].[EC_BOUGHT]';
The fact that we added the new "super-set" constraint first without dropping the earlier one, allows the operation to be a metadata-only operation - it doesn't need to check all the existing data in the bought
table, as it encompasses the existing constraint.
With this, for a given edge to be allowed in the bought
edge, it has to satisfy either of the edge constraint clauses in EC_BOUGHT_NEW
constraint. Hence any edge that is trying to connect valid Customer
to Product
or Supplier
to Product
nodes, is allowed.
Delete edge constraints
The following example first identifies the name of the edge constraint and then deletes the constraint.
-- CREATE node and edge tables
CREATE TABLE Customer
(
ID INTEGER PRIMARY KEY
, CustomerName VARCHAR(100)
)
AS NODE;
GO
CREATE TABLE Product
(
ID INTEGER PRIMARY KEY
, ProductName VARCHAR(100)
) AS NODE;
GO
CREATE TABLE bought
(
PurchaseCount INT
, CONSTRAINT EC_BOUGHT CONNECTION (Customer TO Product)
)
AS EDGE;
GO
-- Return the name of edge constraint.
SELECT name
FROM sys.edge_constraints
WHERE type = 'EC' AND parent_object_id = OBJECT_ID('bought');
GO
-- Delete the primary key constraint.
ALTER TABLE bought
DROP CONSTRAINT EC_BOUGHT;
Modify edge constraints
To modify an edge constraint using Transact-SQL, you must first delete the existing edge constraint, and then re-create it with the new definition.
View edge constraints
The visibility of the metadata in catalog views is limited to securables that a user either owns, or on which the user was granted some permission. For more information, see Metadata Visibility Configuration.
The example returns all edge constraints and their properties for the edge table bought
in the tempdb
database.
-- CREATE node and edge tables
CREATE TABLE Customer
(
ID INTEGER PRIMARY KEY
, CustomerName VARCHAR(100)
)
AS NODE;
GO
CREATE TABLE Supplier
(
ID INTEGER PRIMARY KEY
, SupplierName VARCHAR(100)
)
AS NODE;
GO
CREATE TABLE Product
(
ID INTEGER PRIMARY KEY
, ProductName VARCHAR(100)
)
AS NODE;
-- CREATE edge table with edge constraints.
CREATE TABLE bought
(
PurchaseCount INT
, CONSTRAINT EC_BOUGHT CONNECTION (Customer TO Product, Supplier TO Product)
)
AS EDGE;
-- Query sys.edge_constraints and sys.edge_constraint_clauses to view
-- edge constraint properties.
SELECT
EC.name AS edge_constraint_name
, OBJECT_NAME(EC.parent_object_id) AS edge_table_name
, OBJECT_NAME(ECC.from_object_id) AS from_node_table_name
, OBJECT_NAME(ECC.to_object_id) AS to_node_table_name
, is_disabled
, is_not_trusted
FROM sys.edge_constraints EC
INNER JOIN sys.edge_constraint_clauses ECC
ON EC.object_id = ECC.object_id
WHERE EC.parent_object_id = object_id('bought');