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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
USE tempdb; GO CREATE SCHEMA Basics; GO CREATE TABLE Basics.Node1 ( Node1Id int NOT NULL CONSTRAINT PKNode1 PRIMARY KEY )AS NODE; CREATE TABLE Basics.Node2 ( Node2Id int NOT NULL CONSTRAINT PKNode2 PRIMARY KEY )AS NODE; CREATE TABLE Basics.BetweenNodes1 ( ConnectedSinceTime datetime2(0) NOT NULL CONSTRAINT DFLTBetweenNodes1_ConnectedSinceTime DEFAULT(SYSDATETIME()), CONSTRAINT ECBetweenNodes1_Node1_Node2 CONNECTION(Basics.Node1 TO Basics.Node2)ON DELETE NO ACTION )AS EDGE; |
Then insert a few rows into the two node tables to get things prepared for the edge triggers.
|
1 2 3 4 5 |
INSERT INTO Basics.Node1(Node1Id) VALUES (1001),(1002),(1011),(1012),(1021); INSERT INTO Basics.Node2(Node2Id) VALUES (2011),(2012),(2021); |
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.
|
1 2 3 4 5 6 7 8 |
CREATE OR ALTER VIEW Basics.BetweenNodes1_Manage_Node1_To_Node2 AS SELECT Node1.Node1Id AS From_Node1Id, Node2.Node2Id AS To_Node2Id FROM Basics.Node1, Basics.BetweenNodes1, Basics.Node2 WHERE MATCH(Node1-(BetweenNodes1)->Node2); |
There is no data in the edge yet, but before I insert any, I will create the following INSTEAD OF INSERT trigger.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
CREATE OR ALTER TRIGGER Basics.BetweenNodes1_Manage_Node1_To_Node2$InsertTrigger ON Basics.BetweenNodes1_Manage_Node1_To_Node2 INSTEAD OF INSERT AS SET NOCOUNT ON; --note, to keep it simple, only including the insert statement. Could --use more error handling for a production version of the trigger BEGIN INSERT INTO Basics.BetweenNodes1($From_id, $To_id) SELECT Node1.$node_id, Node2.$node_id FROM Inserted JOIN Basics.Node1 ON Node1.Node1Id = inserted.From_Node1Id JOIN Basics.Node2 ON Node2.Node2Id = inserted.To_Node2Id END; |
Now insert one row into the table, just using the keys of the nodes:
|
1 2 |
INSERT INTO Basics.BetweenNodes1_Manage_Node1_To_Node2(From_Node1Id, To_Node2Id) VALUES (1001, 2011); |
Then view the output:
|
1 2 |
SELECT * FROM Basics.BetweenNodes1_Manage_Node1_To_Node2; |
You can see the rows have been inserted:
Node1Id Node2Id
----------- -----------
1001 2011
Then add multiple edge rows:
|
1 2 |
INSERT INTO Basics.BetweenNodes1_Manage_Node1_To_Node2(From_Node1Id, To_Node2Id) VALUES (1002, 2012),(1002,2021); |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
CREATE OR ALTER TRIGGER Basics.BetweenNodes1_Manage_Node1_To_Node2$UpdateTrigger ON Basics.BetweenNodes1_Manage_Node1_To_Node2 INSTEAD OF UPDATE AS BEGIN SET NOCOUNT ON; --delete rows in deleted DELETE FROM Basics.BetweenNodes1 FROM Deleted JOIN Basics.Node1 ON Node1.Node1Id = deleted.From_Node1Id JOIN Basics.Node2 ON Node2.Node2Id = deleted.To_Node2Id --get the rows to delete by joining to the table --through the graph keys JOIN Basics.BetweenNodes1 ON Node1.$node_id = BetweenNodes1.$from_id AND Node2.$node_id = BetweenNodes1.$to_id; --add the rows from inserted INSERT INTO Basics.BetweenNodes1($From_id, $To_id) SELECT Node1.$node_id, Node2.$node_id FROM Inserted JOIN Basics.Node1 ON Node1.Node1Id = inserted.From_Node1Id JOIN Basics.Node2 ON Node2.Node2Id = inserted.To_Node2Id; END; |
This is how the data looks now:
Node1Id Node2Id
----------- -----------
1001 2011
1002 2012
1002 2021
Update the rows using the view:
|
1 2 3 4 5 |
UPDATE Basics.BetweenNodes1_Manage_Node1_To_Node2 SET From_Node1Id = 1001, To_Node2Id = 2012 WHERE From_Node1Id = 1001 AND To_Node2Id = 2011; |
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.
|
1 2 |
UPDATE Basics.BetweenNodes1_Manage_Node1_To_Node2 SET 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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
CREATE OR ALTER TRIGGER Basics.BetweenNodes1_Manage_Node1_To_Node2$UpdateTrigger ON Basics.BetweenNodes1_Manage_Node1_To_Node2 INSTEAD OF DELETE AS BEGIN SET NOCOUNT ON; --delete rows in deleted DELETE FROM Basics.BetweenNodes1 FROM Deleted JOIN Basics.Node1 ON Node1.Node1Id = deleted.From_Node1Id JOIN Basics.Node2 ON Node2.Node2Id = deleted.To_Node2Id --get the rows to delete by joining to the table --through the graph keys JOIN Basics.BetweenNodes1 ON Node1.$node_id = BetweenNodes1.$from_id AND Node2.$node_id = BetweenNodes1.$to_id; END; |
Delete the row where node1Id = 1001:
|
1 2 |
DELETE FROM Basics.BetweenNodes1_Manage_Node1_To_Node2 WHERE From_Node1Id = 1001; |
Check out the data and you will see the following:
Node1Id Node2Id
----------- -----------
1002 2011
1002 2011
Finally, delete the last two rows:
|
1 |
DELETE FROM Basics.BetweenNodes1_Manage_Node1_To_Node2; |
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.
Load comments