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.<\/p>\n
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.<\/p>\n
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:<\/p>\n
CREATE TABLE dbo.Node1(Name varchar(20)) AS NODE;\r\nCREATE TABLE dbo.Node2(Name varchar(20)) AS NODE;\r\nCREATE TABLE dbo.Node3(Name varchar(20)) AS NODE;\r\nCREATE TABLE dbo.Node4(Name varchar(20)) AS NODE;<\/pre>\nFor one edge table, I will create it to allow any nodes to connect:<\/p>\n
CREATE TABLE dbo.Edge1 AS EDGE;<\/pre>\nAnd 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.<\/p>\n
CREATE TABLE dbo.Edge2\r\n(\r\n\tCONSTRAINT EC_Edge2 CONNECTION (dbo.Node1 TO dbo.Node2,\r\n\t\t\t\t dbo.Node2 TO dbo.Node2) \r\n ON DELETE CASCADE\r\n) AS EDGE;\r\nCREATE TABLE dbo.Edge3\r\n(\r\n CONSTRAINT EC_Edge3 CONNECTION (dbo.Node1 TO dbo.Node2,\r\n dbo.Node2 TO dbo.Node3,\r\n dbo.Node2 TO dbo.Node1) \r\n ON DELETE NO ACTION\r\n) AS EDGE;<\/pre>\nTo 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<\/a>).<\/p>\n
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?)<\/p>\n
So, here first is the query of sys.edge_constraints, showing the constraints that exist:<\/p>\n
SELECT object_id AS edge_object_id,\r\n CONCAT(QUOTENAME(OBJECT_SCHEMA_NAME(\r\n edge_constraints.parent_object_id)),'.',\r\n QUOTENAME(OBJECT_NAME(edge_constraints.parent_object_id))) \r\n AS ObjectName,\r\n QUOTENAME(name) AS EdgeConstraintName, \r\n delete_referential_action_desc AS DeleteAction\r\nFROM sys.edge_constraints;<\/pre>\nThis returns the constraints and what happens on delete:<\/p>\n
edge_object_id ObjectName EdgeConstraintName DeleteAction\r\n-------------- ---------------- --------------------- ----------------\r\n725577623 [dbo].[Edge2] [EC_Edge2] CASCADE\r\n789577851 [dbo].[Edge3] [EC_Edge3] NO ACTION<\/pre>\nNext to get the nodes that can be involved in the relationship, use:<\/p>\n
SELECT object_id AS edge_object_id, \r\n\t CONCAT(QUOTENAME(OBJECT_SCHEMA_NAME(from_object_id)), \r\n\t '.',QUOTENAME(OBJECT_NAME(from_object_id))) \r\n AS FromNode,\r\n\t from_object_id,\r\n\t CONCAT(QUOTENAME(OBJECT_SCHEMA_NAME(to_object_id)), \r\n\t '.',QUOTENAME(OBJECT_NAME(to_object_id))) \r\n AS ToNode,\r\n\t to_object_id\r\nFROM sys.edge_constraint_clauses;<\/pre>\nThis returns (less the from and to object_id values):<\/p>\n
edge_object_id FromNode from_object_id ToNode to_object_id\r\n-------------- ---------------- -------------- -------------- ------------\r\n757577737 [dbo].[Node2] 645577338 [dbo].[Node1] 629577281\r\n725577623 [dbo].[Node1] 629577281 [dbo].[Node2] 645577338\r\n725577623 [dbo].[Node2] 645577338 [dbo].[Node2] 645577338\r\n757577737 [dbo].[Node1] 629577281 [dbo].[Node2] 645577338\r\n757577737 [dbo].[Node2] 645577338 [dbo].[Node3] 661577395<\/pre>\nNow I am going to put these together to get the conditions and the constraints:<\/p>\n
WITH Constraints AS (\r\nSELECT object_id AS edge_object_id,\r\n\t CONCAT(QUOTENAME(OBJECT_SCHEMA_NAME(edge_constraints.parent_object_id)), \r\n '.',QUOTENAME(OBJECT_NAME(edge_constraints.parent_object_id))) AS ObjectName,\r\n\t QUOTENAME(name) AS EdgeConstraintName, \r\n\t delete_referential_action_desc AS DeleteAction\r\nFROM sys.edge_constraints),\r\nClauses AS (\r\nSELECT object_id AS edge_object_id, \r\n CONCAT(QUOTENAME(OBJECT_SCHEMA_NAME(from_object_id)), '.',\r\n QUOTENAME(OBJECT_NAME(from_object_id))) AS FromNode,\r\n from_object_id,\r\n CONCAT(QUOTENAME(OBJECT_SCHEMA_NAME(to_object_id)), '.',\r\n QUOTENAME(OBJECT_NAME(to_object_id))) AS ToNode,\r\n to_object_id\r\nFROM sys.edge_constraint_clauses)\r\nSELECT Constraints.ObjectName, Constraints.EdgeConstraintName, Constraints.DeleteAction, \r\n Clauses.FromNode, Clauses.ToNode\r\nFROM constraints\r\n\t\tJOIN Clauses\r\n\t\t\tON Clauses.edge_object_id = Constraints.edge_object_id;\r\n<\/pre>\nThie returns the rough output:<\/p>\n
ObjectName EdgeConstraintName DeleteAction FromNode ToNode\r\n-------------- -------------------- ------------- --------------- -----------------\r\n[dbo].[Edge2] [EC_Edge2] CASCADE [dbo].[Node2] [dbo].[Node2]\r\n[dbo].[Edge2] [EC_Edge2] CASCADE [dbo].[Node1] [dbo].[Node2]\r\n[dbo].[Edge3] [EC_Edge3] NO_ACTION [dbo].[Node1] [dbo].[Node2]\r\n[dbo].[Edge3] [EC_Edge3] NO_ACTION [dbo].[Node2] [dbo].[Node3]\r\n[dbo].[Edge3] [EC_Edge3] NO_ACTION [dbo].[Node2] [dbo].[Node1]<\/pre>\nIn 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.)<\/p>\n
WITH Constraints AS (\r\nSELECT object_id AS edge_object_id,\r\n\t CONCAT(QUOTENAME(OBJECT_SCHEMA_NAME(edge_constraints.parent_object_id)), \r\n\t\t\t '.',QUOTENAME(OBJECT_NAME(edge_constraints.parent_object_id))) AS ObjectName,\r\n\t QUOTENAME(name) AS EdgeConstraintName, \r\n\t delete_referential_action_desc AS DeleteAction\r\nFROM sys.edge_constraints),\r\nClauses AS (SELECT object_id AS edge_object_id, \r\n\t CONCAT(QUOTENAME(OBJECT_SCHEMA_NAME(from_object_id)), '.',\r\n QUOTENAME(OBJECT_NAME(from_object_id))) AS FromNode,\r\n\t from_object_id,\r\n\t CONCAT(QUOTENAME(OBJECT_SCHEMA_NAME(to_object_id)), '.',\r\n QUOTENAME(OBJECT_NAME(to_object_id))) AS ToNode,\r\n\t to_object_id\r\nFROM sys.edge_constraint_clauses)\r\nSELECT Constraints.ObjectName, Constraints.EdgeConstraintName, \r\n Constraints.DeleteAction,\r\n\t --aggregate allowable connections\r\n\t STRING_AGG(CONCAT('{',Clauses.FromNode,' -> '\r\n ,Clauses.ToNode,'}'),'; ') AS AllowedConnections\r\nFROM constraints\r\n\t\tJOIN Clauses\r\n\t\t\tON Clauses.edge_object_id = Constraints.edge_object_id\r\nGROUP BY Constraints.ObjectName, Constraints.EdgeConstraintName, Constraints.DeleteAction\r\nUNION ALL \r\n--add in any edge that does not have a constraint, and indicate it can be used for any connection\r\nSELECT CONCAT(QUOTENAME(OBJECT_SCHEMA_NAME(object_id)),'.',QUOTENAME(name)) AS ObjectName, \r\n\t 'N\\A','N\\A', '{Any Node -> Any Node}'\r\nFROM sys.tables\r\nWHERE tables.is_edge = 1\r\n AND NOT EXISTS (SELECT *\r\n\t\t\t\t FROM sys.edge_constraints\r\n\t\t\t\t WHERE edge_constraints.parent_object_id = tables.object_id);<\/pre>\nThis returns:<\/p>\n
ObjectName EdgeConstraintName DeleteAction AllowedConnections\r\n--------------- ------------------- --------------- --------------------------------------------------------------------------------------------------------\r\n[dbo].[Edge3]\t[EC_Edge3] NO_ACTION {[dbo].[Node2] -> [dbo].[Node3]}; {[dbo].[Node2] -> [dbo].[Node1]}; {[dbo].[Node1] -> [dbo].[Node2]}\r\n[dbo].[Edge2]\t[EC_Edge2] CASCADE {[dbo].[Node1] -> [dbo].[Node2]}; {[dbo].[Node2] -> [dbo].[Node2]}\r\n[dbo].[Edge1]\tN\\A N\\A {Any Node -> Any Node}<\/pre>\nFinally, this next query lists things in a node centric format:<\/p>\n
WITH UnconstrainedEdgeMix AS (\r\n--output unconstrained nodes as Any Node, rather than the cross product of all node types\r\nSELECT CONCAT(QUOTENAME(OBJECT_SCHEMA_NAME(edges.object_id)), \r\n\t\t\t '.',QUOTENAME(OBJECT_NAME(edges.object_id))) AS EdgeName,\r\n\t\t\t CAST(NULL AS int) AS FromNodeId, -CAST(NULL AS int) AS ToNodeId,\r\n\t\t\t 'Orphan' AS DeleteAction\r\nFROM sys.tables AS edges\r\nWHERE edges.is_edge = 1\r\n AND NOT EXISTS (SELECT *\r\n\t FROM sys.edge_constraints\r\n\t\t\t\t WHERE edges.object_id = edge_constraints.parent_object_id )\r\n), BaseRows AS (\r\nSELECT EdgeName, FromNodeId, ToNodeId, UnconstrainedEdgeMix.DeleteAction\r\nFROM UnconstrainedEdgeMix \r\nUNION ALL\r\n--add the constrained edges in, with their id and actions\r\nSELECT CONCAT(QUOTENAME(OBJECT_SCHEMA_NAME(edge_constraints.parent_object_id)), \r\n\t\t\t '.',QUOTENAME(OBJECT_NAME(edge_constraints.parent_object_id))) AS EdgeName,\r\n\t from_object_id AS FromNodeId,\r\n\t to_object_id AS ToNodeId,\r\n\t edge_constraints.delete_referential_action_desc AS DeleteAction\r\nFROM sys.edge_constraint_clauses\r\n\t\tJOIN sys.edge_constraints\r\n\t\t\tON edge_constraints.object_id = edge_constraint_clauses.object_id\r\n),\r\n--And the last CTE lets you add filters to the query so you can just look for what Node1 can connect to \r\n--explicitly (by name) or implicitly (by looking for Any in the node and schema).\r\nFilterFrom AS (\r\nSELECT COALESCE(OBJECT_SCHEMA_NAME(BaseRows.FromNodeId),'Any') AS NodeSchema,\r\n\t\tCOALESCE(OBJECT_NAME(BaseRows.FromNodeId),'Any') AS Node, EdgeName, 'From' AS Relationship, DeleteAction\r\nFROM BaseRows\r\nUNION ALL\r\nSELECT COALESCE(OBJECT_SCHEMA_NAME(BaseRows.FromNodeId),'Any') AS NodeSchema,\r\n\t COALESCE(OBJECT_NAME(BaseRows.FromNodeId),'Any') AS Node, EdgeName, 'To' AS Relationship, DeleteAction\r\nFROM BaseRows)\r\nSELECT *\r\nFROM FilterFrom\r\nORDER BY FilterFrom.NodeSchema, FilterFrom.Node, FilterFrom.Relationship, FilterFrom.EdgeName;<\/pre>\nThis outputs:<\/p>\n
NodeSchema Node EdgeName Relationship DeleteAction\r\n------------- --------- --------------- ------------ -----------------------\r\nAny Any [dbo].[Edge1] From Orphan\r\nAny Any [dbo].[Edge1] To Orphan\r\ndbo Node1 [dbo].[Edge2] From CASCADE\r\ndbo Node1 [dbo].[Edge3] From NO_ACTION\r\ndbo Node1 [dbo].[Edge2] To CASCADE\r\ndbo Node1 [dbo].[Edge3] To NO_ACTION\r\ndbo Node2 [dbo].[Edge2] From CASCADE\r\ndbo Node2 [dbo].[Edge3] From NO_ACTION\r\ndbo Node2 [dbo].[Edge3] From NO_ACTION\r\ndbo Node2 [dbo].[Edge2] To CASCADE\r\ndbo Node2 [dbo].[Edge3] To NO_ACTION\r\ndbo Node2 [dbo].[Edge3] To NO_ACTION<\/pre>\nAs 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\/<\/a>).<\/p>\n
As usual, you can find the primary useful queries on my github page as both metadata queries<\/a> and in my SQL Prompt snippet<\/a> repos.<\/p>\n","protected":false},"excerpt":{"rendered":"
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……<\/p>\n","protected":false},"author":56085,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[],"coauthors":[19684],"class_list":["post-90249","post","type-post","status-publish","format-standard","hentry","category-blogs"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/90249","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/56085"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=90249"}],"version-history":[{"count":4,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/90249\/revisions"}],"predecessor-version":[{"id":90253,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/90249\/revisions\/90253"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=90249"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=90249"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=90249"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=90249"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}