How to modify a SQL Server Graph Edge with T-SQL

As I have been writing a section on SQL Server graph tables in my Database Design book, (and prepping for a hopeful book on the subject next year), I find that there are a few really annoying things about dealing with graph tables. This blog serves to clear up the first, most annoying of them. Inserting, updating, and deleting edges.

Because the key values in the graph database structures are hidden, you can’t just insert a new edge without translating your table’s key values to the graph database internal values. Edges aren’t even available for an update of the from or to references. As I wrote stored procedures to do this, I realized “why not use a view and trigger to make this happen”. So I did. The result is that I can insert, delete, and even update graph tables using normal SQL syntax. What makes this better than the stored procedure is that I can insert multiple rows simultaneously.

I haven’t tried to do this with large number of rows yet, but either way it will work fine for one or two rows in any sized table with proper indexing. To demonstrate this, I started with the following tables. There is no theme to the tables, there are just tables I have used to test out concepts like this:

Then insert a few rows into the two node tables to get things prepared for the edge triggers.

Next I create the following view that uses regular joins between the nodes to fetch the internal details of the graph structures. All that will be output by the view is just the primary key values of the two tables.

There is no data in the edge yet, but before I insert any, I will create the following INSTEAD OF INSERT trigger.

Now insert one row into the table, just using the keys of the nodes:

Then view the output:

You can see the rows have been inserted:

Node1Id     Node2Id
----------- -----------
1001        2011

Then add multiple edge rows:

Those rows were also added:

Node1Id     Node2Id
----------- -----------
1001        2011
1002        2012
1002        2021

Next I add an update trigger. You can’t update an edge, so this turns into a delete, and then an update.

This is how the data looks now:

Node1Id     Node2Id
----------- -----------
1001        2011
1002        2012
1002        2021

Update the rows using the view:

You can see that the 1001 row now is connected to 2012:

Node1Id     Node2Id
----------- -----------
1001        2012
1002        2012
1002        2021

Now, update all of the rows to be connected to Node2Id = 2011.

Check out the data and you will see the following:

Node1Id     Node2Id
----------- -----------
1001        2011
1002        2011
1002        2011

Finally, I will implement an INSTEAD OF DELETE trigger:

Delete the row where node1Id = 1001:

Check out the data and you will see the following:

Node1Id     Node2Id
----------- -----------
1002        2011
1002        2011

Finally, delete the last two rows:

The table is empty now. Next trick is to build a code generator to build a view for every edge without having to write this code over and over.