By
Published
Mar 23 2019 05:58 PM
1,262
Views
First published on MSDN on Sep 28, 2018
SQL Server 2017 and Azure SQL Database introduced native graph database capabilities used to model many-to-many relationships. The first implementation of SQL Graph introduced support for nodes to represent entities, edges to represent relationships and a new MATCH predicate to support graph pattern matching and traversal.
We will be further expanding the graph database capabilities with several new features. In this blog we will discuss one of these features that is now available for public preview in SQL Server 2019 , Edge Constraints on Graph Edge Tables .
In the first release of SQL Graph, an edge could connect any node to any other node in the database. With Edge Constraints users can enforce specific semantics on the edge tables. The constraints also help in maintaining data integrity. This post describes how you can create and use edge constraints in a graph database. We will use the following graph schema created in the WideWorldImporters database for the samples discussed here.
Imagine that in the schema above, WideWorldImporters would like to create an edge constraint on the Bought edge so that only a Customer could buy a StockItem but not vice versa. That is, insert of an edge that goes from StockItem to Customer should fail. Here is how they can create the Bought edge with an edge constraint on it.
[code language="sql"]
CREATE TABLE Bought
(
[PurchasedCount] BIGINT,
CONSTRAINT EC_BOUGHT CONNECTION (Customers TO StockItems)
) AS EDGE;
GO
[/code]
The following insert succeeds, because we are trying to insert an edge that connects Customers to StockItems :
[code language="sql"]
DECLARE @pc INT = 10
DECLARE @CID INT = 1
DECLARE @SID INT = 10
INSERT INTO Bought ($from_id, $to_id, PurchasedCount)
SELECT C.$node_id, S.$node_id, @pc
FROM StockItems S, Customers C
WHERE S.StockItemID = @SID AND C.CustomerID = @CID
[/code]
However, if an application user tries to insert an edge that goes from StockItems to Customers , the insert will fail:
[code language="sql"]
DECLARE @pc INT = 10
DECLARE @CID INT = 1
DECLARE @SID INT = 10
INSERT INTO Bought ($from_id, $to_id, PurchasedCount)
SELECT S.$node_id, C.$node_id, @pc
FROM StockItems S, Customers C
WHERE S.StockItemID = @SID AND C.CustomerID = @CID
Msg 547, Level 16, State 0, Line 46
The INSERT statement conflicted with the EDGE constraint "EC_BOUGHT". The conflict occurred in database "WideWorldImporters", table "dbo.Bought".
The statement has been terminated.
[/code]
The Bought edge table will only allow inserting edges that go from Customers to StockItems . Any other type of edge inserts will fail because they will not satisfy the edge constraint on this table.
Imagine that WideWorldImporters has now created a new node table, Suppliers , in their schema and they would like to allow Suppliers to buy StockItems. To enable inserting edges that go from Suppliers to StockItems, they will now need to add a new edge constraint to the existing edge table. Here is how they can do so.
First create the Suppliers node table in your schema.
[code language="sql"]
-- Create the Suppliers node table.
CREATE TABLE Suppliers
(
[SupplierID] INTEGER NOT NULL,
[SupplierName] NVARCHAR(100) NOT NULL,
[PhoneNumber] NVARCHAR(20) NOT NULL
) AS NODE
GO
[/code]
Now add a new edge constraint to the Bought edge table, which already has an edge constraint on it. The recommended approach is to add a new edge constraint before deleting the old one, as it is more efficient. For example, adding a new edge constraint in this scenario, before dropping the old one will be a metadata only operation as compared to being a size of data operation in the absence of any constraint on the Bought table.
[code language="sql"]
-- Add a new edge constraint to the Bought edge table
-- which connects Customers to StockItems and Suppliers to StockItems.
ALTER TABLE Bought ADD CONSTRAINT EC_BOUGHT1 CONNECTION
(
Customers TO StockItems,
Suppliers TO StockItems
)
GO
-- Now drop EC_BOUGHT which connected only Customers to StockItems.
ALTER TABLE Bought DROP CONSTRAINT EC_BOUGHT
GO
[/code]
Now an edge that either connects Customers to StockItems or one that connects Suppliers to StockItems will be allowed in the Bought edge table.
Note that an edge constraint can have multiple edge constraint clauses. Each pair of FROM and TO node tables form an edge constraint clause. All the clauses in a given edge constraint are applied as disjunction. That is, an edge to be allowed in that edge table has to satisfy any one of the edge constraint clauses.
Creating two separate edge constraints with different edge constraint clauses is not same as creating one edge constraint with multiple clauses. If multiple edge constraints are created on a single edge table, for an edge to be allowed in that edge table, it has to satisfy ALL edge constraint clauses. Consider the following example.
[code language="sql"]
-- Instead of adding a new edge constraint with 2 clauses, in the above example
-- assume that we were adding a new constraint with only one clause to an empty
-- edge table.
ALTER TABLE Bought ADD CONSTRAINT EC_BOUGHT1 CONNECTION (Suppliers TO StockItems)
GO
[/code]
Now the edge table Bought has two edge constraints on it, EC_BOUGHT and EC_BOUGHT1 . Any edge that is inserted into the Bought edge table, must satisfy both the constraints. Since no edge can satisfy both EC_BOUGHT and EC_BOUGHT1 , the edge must remain empty.
In the previous release, if a node was deleted from the graph, the connecting edges (incoming or outgoing) were left behind dangling in the graph. Edge constraints prevent users from deleting a node before deleting connecting edges. This helps in maintaining data integrity in the graph. The following example demonstrates this:
[code language="sql"]
-- Bought edge has edge constraint EC_BOUGHT1, which
-- goes from Customers to StockItems and Suppliers to StockItems.
DELETE FROM Customers WHERE CustomerID = 1;
Msg 547, Level 16, State 0, Line 74
The DELETE statement conflicted with the EDGE REFERENCE constraint "EC_BOUGHT1". The conflict occurred in database "WideWorldImporters", table "dbo.Bought".
The statement has been terminated.
[/code]
In the previous release, users could also insert $node_ids that didn’t exist in the edge table. With edge constraints, we now validate that the nodes exist in the respective node tables.
[code language="sql"]
-- The Customers node with id 4000 does not exist in the database
DECLARE @pc INT = 10
INSERT INTO Bought ($from_id, $to_id, PurchasedCount)
VALUES ('{"type":"node","schema":"dbo","table":"Customers","id":4000}',
'{"type":"node","schema":"dbo","table":"StockItems","id":0}',
@pc)
GO
Msg 547, Level 16, State 0, Line 63
The INSERT statement conflicted with the EDGE constraint "EC_BOUGHT1". The conflict occurred in database "WideWorldImporters", table "dbo.Bought".
The statement has been terminated.
[/code]
Two new system catalog views are added which can be queried to get details about the edge constraints in your schema:
The following query will fetch details about the edge constraints from these views:
[code language="sql"]
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')
[/code]
To run the examples shown here, download and restore the WideWorldImporters database backup (follow these setup instructions ) and then run the following scripts to create and populate the node and edge tables:
Edge Constraints will help in maintaining data integrity in your graph database and also in enforcing specific semantics on an edge table. Please give it a try on SQL Server 2019 and leave us some feedback. We would love to hear from you about this feature.
SQL Server 2017 and Azure SQL Database introduced native graph database capabilities used to model many-to-many relationships. The first implementation of SQL Graph introduced support for nodes to represent entities, edges to represent relationships and a new MATCH predicate to support graph pattern matching and traversal.
We will be further expanding the graph database capabilities with several new features. In this blog we will discuss one of these features that is now available for public preview in SQL Server 2019 , Edge Constraints on Graph Edge Tables .
In the first release of SQL Graph, an edge could connect any node to any other node in the database. With Edge Constraints users can enforce specific semantics on the edge tables. The constraints also help in maintaining data integrity. This post describes how you can create and use edge constraints in a graph database. We will use the following graph schema created in the WideWorldImporters database for the samples discussed here.
Graph Schema for Edge Constraints examples
Creating a new edge table with edge constraints
Imagine that in the schema above, WideWorldImporters would like to create an edge constraint on the Bought edge so that only a Customer could buy a StockItem but not vice versa. That is, insert of an edge that goes from StockItem to Customer should fail. Here is how they can create the Bought edge with an edge constraint on it.
[code language="sql"]
CREATE TABLE Bought
(
[PurchasedCount] BIGINT,
CONSTRAINT EC_BOUGHT CONNECTION (Customers TO StockItems)
) AS EDGE;
GO
[/code]
The following insert succeeds, because we are trying to insert an edge that connects Customers to StockItems :
[code language="sql"]
DECLARE @pc INT = 10
DECLARE @CID INT = 1
DECLARE @SID INT = 10
INSERT INTO Bought ($from_id, $to_id, PurchasedCount)
SELECT C.$node_id, S.$node_id, @pc
FROM StockItems S, Customers C
WHERE S.StockItemID = @SID AND C.CustomerID = @CID
[/code]
However, if an application user tries to insert an edge that goes from StockItems to Customers , the insert will fail:
[code language="sql"]
DECLARE @pc INT = 10
DECLARE @CID INT = 1
DECLARE @SID INT = 10
INSERT INTO Bought ($from_id, $to_id, PurchasedCount)
SELECT S.$node_id, C.$node_id, @pc
FROM StockItems S, Customers C
WHERE S.StockItemID = @SID AND C.CustomerID = @CID
Msg 547, Level 16, State 0, Line 46
The INSERT statement conflicted with the EDGE constraint "EC_BOUGHT". The conflict occurred in database "WideWorldImporters", table "dbo.Bought".
The statement has been terminated.
[/code]
The Bought edge table will only allow inserting edges that go from Customers to StockItems . Any other type of edge inserts will fail because they will not satisfy the edge constraint on this table.
Adding edge constraints to an existing edge table
Imagine that WideWorldImporters has now created a new node table, Suppliers , in their schema and they would like to allow Suppliers to buy StockItems. To enable inserting edges that go from Suppliers to StockItems, they will now need to add a new edge constraint to the existing edge table. Here is how they can do so.
First create the Suppliers node table in your schema.
[code language="sql"]
-- Create the Suppliers node table.
CREATE TABLE Suppliers
(
[SupplierID] INTEGER NOT NULL,
[SupplierName] NVARCHAR(100) NOT NULL,
[PhoneNumber] NVARCHAR(20) NOT NULL
) AS NODE
GO
[/code]
Now add a new edge constraint to the Bought edge table, which already has an edge constraint on it. The recommended approach is to add a new edge constraint before deleting the old one, as it is more efficient. For example, adding a new edge constraint in this scenario, before dropping the old one will be a metadata only operation as compared to being a size of data operation in the absence of any constraint on the Bought table.
[code language="sql"]
-- Add a new edge constraint to the Bought edge table
-- which connects Customers to StockItems and Suppliers to StockItems.
ALTER TABLE Bought ADD CONSTRAINT EC_BOUGHT1 CONNECTION
(
Customers TO StockItems,
Suppliers TO StockItems
)
GO
-- Now drop EC_BOUGHT which connected only Customers to StockItems.
ALTER TABLE Bought DROP CONSTRAINT EC_BOUGHT
GO
[/code]
Now an edge that either connects Customers to StockItems or one that connects Suppliers to StockItems will be allowed in the Bought edge table.
Note that an edge constraint can have multiple edge constraint clauses. Each pair of FROM and TO node tables form an edge constraint clause. All the clauses in a given edge constraint are applied as disjunction. That is, an edge to be allowed in that edge table has to satisfy any one of the edge constraint clauses.
Creating two separate edge constraints with different edge constraint clauses is not same as creating one edge constraint with multiple clauses. If multiple edge constraints are created on a single edge table, for an edge to be allowed in that edge table, it has to satisfy ALL edge constraint clauses. Consider the following example.
[code language="sql"]
-- Instead of adding a new edge constraint with 2 clauses, in the above example
-- assume that we were adding a new constraint with only one clause to an empty
-- edge table.
ALTER TABLE Bought ADD CONSTRAINT EC_BOUGHT1 CONNECTION (Suppliers TO StockItems)
GO
[/code]
Now the edge table Bought has two edge constraints on it, EC_BOUGHT and EC_BOUGHT1 . Any edge that is inserted into the Bought edge table, must satisfy both the constraints. Since no edge can satisfy both EC_BOUGHT and EC_BOUGHT1 , the edge must remain empty.
Maintaining data integrity with edge constraints
In the previous release, if a node was deleted from the graph, the connecting edges (incoming or outgoing) were left behind dangling in the graph. Edge constraints prevent users from deleting a node before deleting connecting edges. This helps in maintaining data integrity in the graph. The following example demonstrates this:
[code language="sql"]
-- Bought edge has edge constraint EC_BOUGHT1, which
-- goes from Customers to StockItems and Suppliers to StockItems.
DELETE FROM Customers WHERE CustomerID = 1;
Msg 547, Level 16, State 0, Line 74
The DELETE statement conflicted with the EDGE REFERENCE constraint "EC_BOUGHT1". The conflict occurred in database "WideWorldImporters", table "dbo.Bought".
The statement has been terminated.
[/code]
In the previous release, users could also insert $node_ids that didn’t exist in the edge table. With edge constraints, we now validate that the nodes exist in the respective node tables.
[code language="sql"]
-- The Customers node with id 4000 does not exist in the database
DECLARE @pc INT = 10
INSERT INTO Bought ($from_id, $to_id, PurchasedCount)
VALUES ('{"type":"node","schema":"dbo","table":"Customers","id":4000}',
'{"type":"node","schema":"dbo","table":"StockItems","id":0}',
@pc)
GO
Msg 547, Level 16, State 0, Line 63
The INSERT statement conflicted with the EDGE constraint "EC_BOUGHT1". The conflict occurred in database "WideWorldImporters", table "dbo.Bought".
The statement has been terminated.
[/code]
Viewing edge constraint details
Two new system catalog views are added which can be queried to get details about the edge constraints in your schema:
The following query will fetch details about the edge constraints from these views:
[code language="sql"]
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')
[/code]
| edge_constraint_name | edge_table_name | from_node_table_name | to_node_table_name | is_disabled | is_not_trusted |
| EC_BOUGHT1 | Bought | Suppliers | StockItems | 0 | 0 |
| EC_BOUGHT1 | Bought | Customers | StockItems | 0 | 0 |
Sample Database Details
To run the examples shown here, download and restore the WideWorldImporters database backup (follow these setup instructions ) and then run the following scripts to create and populate the node and edge tables:
Conclusion
Edge Constraints will help in maintaining data integrity in your graph database and also in enforcing specific semantics on an edge table. Please give it a try on SQL Server 2019 and leave us some feedback. We would love to hear from you about this feature.