PASS Data Community Summit logo

PASS Data Community Summit 2023

Get all the latest announcements direct to your inbox

Determining actions you can take with Edge and Node tables in SQL Server

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:

For one edge table, I will create it to allow any nodes to connect:

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.

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:

This returns the constraints and what happens on delete:

Next to get the nodes that can be involved in the relationship, use:

This returns (less the from and to object_id values):

Now I am going to put these together to get the conditions and the constraints:

Thie returns the rough output:

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.)

This returns:

Finally, this next query lists things in a node centric format:

This outputs:

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.