One of the interesting things about working with many-to-many relationships in SQL Server with graph tables instead of a relational table is that unlike a relational many-to-many table, by default an edge may can implement relationships from lots of different tables (nodes). You can also limit what nodes can be related using which edges.
For example, say you have 4 nodes and 2 edges, both of the edges, by default, each edge would allow relationships from each node to itself, or each node to each other node. It can all get a bit complicated to figure out if you have a lot of objects (and to be fair, you probably also want to be able to check to make sure your objects are configured as you expect.
In this blog, I will demonstrate how to determine, given a given edge or node, what operations are possible. To demonstrate, I will use the following nodes and edges:
CREATE TABLE dbo.Node1(Name varchar(20)) AS NODE; CREATE TABLE dbo.Node2(Name varchar(20)) AS NODE; CREATE TABLE dbo.Node3(Name varchar(20)) AS NODE; CREATE TABLE dbo.Node4(Name varchar(20)) AS NODE;
For one edge table, I will create it to allow any nodes to connect:
CREATE TABLE dbo.Edge1 AS EDGE;
And then another edge, but this one will have 2 conditions that are defined to allow cascading deletes (so if either node is deleted, the edge is removed), and one that is requires you to remove the edge to remove the node.
CREATE TABLE dbo.Edge2
(
CONSTRAINT EC_Edge2 CONNECTION (dbo.Node1 TO dbo.Node2,
dbo.Node2 TO dbo.Node2)
ON DELETE CASCADE
) AS EDGE;
CREATE TABLE dbo.Edge3
(
CONSTRAINT EC_Edge3 CONNECTION (dbo.Node1 TO dbo.Node2,
dbo.Node2 TO dbo.Node3,
dbo.Node2 TO dbo.Node1)
ON DELETE NO ACTION
) AS EDGE;
To find the metadata about these objects, we can use a couple of base catalog views. sys.edge_constraints and sys.edge_constraint_clauses. sys.edge_constraints is the typical extension of sys.objects, with one row per edge constraint (which is itself an object, like other constraints.) sys.edge_constraint_clauses gives you one row per the node to node relationship. (Note, the query allows for the case where you have more than one edge connection, though that is not generally something you should generally implement (Covered in this blog).
I want to have two views of the metadata, one in the context of the node (what edges exist that I can insert into?) and from the context of the edge (what nodes can be involved in a relationship with the edge?)
So, here first is the query of sys.edge_constraints, showing the constraints that exist:
SELECT object_id AS edge_object_id,
CONCAT(QUOTENAME(OBJECT_SCHEMA_NAME(
edge_constraints.parent_object_id)),'.',
QUOTENAME(OBJECT_NAME(edge_constraints.parent_object_id)))
AS ObjectName,
QUOTENAME(name) AS EdgeConstraintName,
delete_referential_action_desc AS DeleteAction
FROM sys.edge_constraints;
This returns the constraints and what happens on delete:
edge_object_id ObjectName EdgeConstraintName DeleteAction -------------- ---------------- --------------------- ---------------- 725577623 [dbo].[Edge2] [EC_Edge2] CASCADE 789577851 [dbo].[Edge3] [EC_Edge3] NO ACTION
Next to get the nodes that can be involved in the relationship, use:
SELECT object_id AS edge_object_id,
CONCAT(QUOTENAME(OBJECT_SCHEMA_NAME(from_object_id)),
'.',QUOTENAME(OBJECT_NAME(from_object_id)))
AS FromNode,
from_object_id,
CONCAT(QUOTENAME(OBJECT_SCHEMA_NAME(to_object_id)),
'.',QUOTENAME(OBJECT_NAME(to_object_id)))
AS ToNode,
to_object_id
FROM sys.edge_constraint_clauses;
This returns (less the from and to object_id values):
edge_object_id FromNode from_object_id ToNode to_object_id -------------- ---------------- -------------- -------------- ------------ 757577737 [dbo].[Node2] 645577338 [dbo].[Node1] 629577281 725577623 [dbo].[Node1] 629577281 [dbo].[Node2] 645577338 725577623 [dbo].[Node2] 645577338 [dbo].[Node2] 645577338 757577737 [dbo].[Node1] 629577281 [dbo].[Node2] 645577338 757577737 [dbo].[Node2] 645577338 [dbo].[Node3] 661577395
Now I am going to put these together to get the conditions and the constraints:
WITH Constraints AS (
SELECT object_id AS edge_object_id,
CONCAT(QUOTENAME(OBJECT_SCHEMA_NAME(edge_constraints.parent_object_id)),
'.',QUOTENAME(OBJECT_NAME(edge_constraints.parent_object_id))) AS ObjectName,
QUOTENAME(name) AS EdgeConstraintName,
delete_referential_action_desc AS DeleteAction
FROM sys.edge_constraints),
Clauses AS (
SELECT object_id AS edge_object_id,
CONCAT(QUOTENAME(OBJECT_SCHEMA_NAME(from_object_id)), '.',
QUOTENAME(OBJECT_NAME(from_object_id))) AS FromNode,
from_object_id,
CONCAT(QUOTENAME(OBJECT_SCHEMA_NAME(to_object_id)), '.',
QUOTENAME(OBJECT_NAME(to_object_id))) AS ToNode,
to_object_id
FROM sys.edge_constraint_clauses)
SELECT Constraints.ObjectName, Constraints.EdgeConstraintName, Constraints.DeleteAction,
Clauses.FromNode, Clauses.ToNode
FROM constraints
JOIN Clauses
ON Clauses.edge_object_id = Constraints.edge_object_id;
Thie returns the rough output:
ObjectName EdgeConstraintName DeleteAction FromNode ToNode -------------- -------------------- ------------- --------------- ----------------- [dbo].[Edge2] [EC_Edge2] CASCADE [dbo].[Node2] [dbo].[Node2] [dbo].[Edge2] [EC_Edge2] CASCADE [dbo].[Node1] [dbo].[Node2] [dbo].[Edge3] [EC_Edge3] NO_ACTION [dbo].[Node1] [dbo].[Node2] [dbo].[Edge3] [EC_Edge3] NO_ACTION [dbo].[Node2] [dbo].[Node3] [dbo].[Edge3] [EC_Edge3] NO_ACTION [dbo].[Node2] [dbo].[Node1]
In this next query (which is the query I was targeting in the first place), I am going to output an edge centric view, partitioned by object, constraint, giving the delete action and the list of node to node relationships. For edges without an edge constraint, I will use Any Node -> Any Node, rather than listing out every possible permutation of nodes that exist (which would make for a very large list pretty quick.)
WITH Constraints AS (
SELECT object_id AS edge_object_id,
CONCAT(QUOTENAME(OBJECT_SCHEMA_NAME(edge_constraints.parent_object_id)),
'.',QUOTENAME(OBJECT_NAME(edge_constraints.parent_object_id))) AS ObjectName,
QUOTENAME(name) AS EdgeConstraintName,
delete_referential_action_desc AS DeleteAction
FROM sys.edge_constraints),
Clauses AS (SELECT object_id AS edge_object_id,
CONCAT(QUOTENAME(OBJECT_SCHEMA_NAME(from_object_id)), '.',
QUOTENAME(OBJECT_NAME(from_object_id))) AS FromNode,
from_object_id,
CONCAT(QUOTENAME(OBJECT_SCHEMA_NAME(to_object_id)), '.',
QUOTENAME(OBJECT_NAME(to_object_id))) AS ToNode,
to_object_id
FROM sys.edge_constraint_clauses)
SELECT Constraints.ObjectName, Constraints.EdgeConstraintName,
Constraints.DeleteAction,
--aggregate allowable connections
STRING_AGG(CONCAT('{',Clauses.FromNode,' -> '
,Clauses.ToNode,'}'),'; ') AS AllowedConnections
FROM constraints
JOIN Clauses
ON Clauses.edge_object_id = Constraints.edge_object_id
GROUP BY Constraints.ObjectName, Constraints.EdgeConstraintName, Constraints.DeleteAction
UNION ALL
--add in any edge that does not have a constraint, and indicate it can be used for any connection
SELECT CONCAT(QUOTENAME(OBJECT_SCHEMA_NAME(object_id)),'.',QUOTENAME(name)) AS ObjectName,
'N\A','N\A', '{Any Node -> Any Node}'
FROM sys.tables
WHERE tables.is_edge = 1
AND NOT EXISTS (SELECT *
FROM sys.edge_constraints
WHERE edge_constraints.parent_object_id = tables.object_id);
This returns:
ObjectName EdgeConstraintName DeleteAction AllowedConnections
--------------- ------------------- --------------- --------------------------------------------------------------------------------------------------------
[dbo].[Edge3] [EC_Edge3] NO_ACTION {[dbo].[Node2] -> [dbo].[Node3]}; {[dbo].[Node2] -> [dbo].[Node1]}; {[dbo].[Node1] -> [dbo].[Node2]}
[dbo].[Edge2] [EC_Edge2] CASCADE {[dbo].[Node1] -> [dbo].[Node2]}; {[dbo].[Node2] -> [dbo].[Node2]}
[dbo].[Edge1] N\A N\A {Any Node -> Any Node}
Finally, this next query lists things in a node centric format:
WITH UnconstrainedEdgeMix AS (
--output unconstrained nodes as Any Node, rather than the cross product of all node types
SELECT CONCAT(QUOTENAME(OBJECT_SCHEMA_NAME(edges.object_id)),
'.',QUOTENAME(OBJECT_NAME(edges.object_id))) AS EdgeName,
CAST(NULL AS int) AS FromNodeId, -CAST(NULL AS int) AS ToNodeId,
'Orphan' AS DeleteAction
FROM sys.tables AS edges
WHERE edges.is_edge = 1
AND NOT EXISTS (SELECT *
FROM sys.edge_constraints
WHERE edges.object_id = edge_constraints.parent_object_id )
), BaseRows AS (
SELECT EdgeName, FromNodeId, ToNodeId, UnconstrainedEdgeMix.DeleteAction
FROM UnconstrainedEdgeMix
UNION ALL
--add the constrained edges in, with their id and actions
SELECT CONCAT(QUOTENAME(OBJECT_SCHEMA_NAME(edge_constraints.parent_object_id)),
'.',QUOTENAME(OBJECT_NAME(edge_constraints.parent_object_id))) AS EdgeName,
from_object_id AS FromNodeId,
to_object_id AS ToNodeId,
edge_constraints.delete_referential_action_desc AS DeleteAction
FROM sys.edge_constraint_clauses
JOIN sys.edge_constraints
ON edge_constraints.object_id = edge_constraint_clauses.object_id
),
--And the last CTE lets you add filters to the query so you can just look for what Node1 can connect to
--explicitly (by name) or implicitly (by looking for Any in the node and schema).
FilterFrom AS (
SELECT COALESCE(OBJECT_SCHEMA_NAME(BaseRows.FromNodeId),'Any') AS NodeSchema,
COALESCE(OBJECT_NAME(BaseRows.FromNodeId),'Any') AS Node, EdgeName, 'From' AS Relationship, DeleteAction
FROM BaseRows
UNION ALL
SELECT COALESCE(OBJECT_SCHEMA_NAME(BaseRows.FromNodeId),'Any') AS NodeSchema,
COALESCE(OBJECT_NAME(BaseRows.FromNodeId),'Any') AS Node, EdgeName, 'To' AS Relationship, DeleteAction
FROM BaseRows)
SELECT *
FROM FilterFrom
ORDER BY FilterFrom.NodeSchema, FilterFrom.Node, FilterFrom.Relationship, FilterFrom.EdgeName;
This outputs:
NodeSchema Node EdgeName Relationship DeleteAction ------------- --------- --------------- ------------ ----------------------- Any Any [dbo].[Edge1] From Orphan Any Any [dbo].[Edge1] To Orphan dbo Node1 [dbo].[Edge2] From CASCADE dbo Node1 [dbo].[Edge3] From NO_ACTION dbo Node1 [dbo].[Edge2] To CASCADE dbo Node1 [dbo].[Edge3] To NO_ACTION dbo Node2 [dbo].[Edge2] From CASCADE dbo Node2 [dbo].[Edge3] From NO_ACTION dbo Node2 [dbo].[Edge3] From NO_ACTION dbo Node2 [dbo].[Edge2] To CASCADE dbo Node2 [dbo].[Edge3] To NO_ACTION dbo Node2 [dbo].[Edge3] To NO_ACTION
As a reminder, the action of Orphan represents what happens without a constraint (it leaves the edge in the table that references the node(s) you delete. For more details about this scenario, I cover that in the following blog entry: (https://www.red-gate.com/simple-talk/blogs/ways-to-get-and-deal-with-invalid-node-identifiers-in-sql-server-edge-references/).
As usual, you can find the primary useful queries on my github page as both metadata queries and in my SQL Prompt snippet repos.
The post Determining actions you can take with Edge and Node tables in SQL Server appeared first on Simple Talk.
from Simple Talk https://ift.tt/2NxsX9g
via
No comments:
Post a Comment