{"id":90238,"date":"2021-03-12T02:14:33","date_gmt":"2021-03-12T02:14:33","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=90238"},"modified":"2021-03-08T02:14:57","modified_gmt":"2021-03-08T02:14:57","slug":"ways-to-get-and-deal-with-invalid-node-identifiers-in-sql-server-edge-references","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/ways-to-get-and-deal-with-invalid-node-identifiers-in-sql-server-edge-references\/","title":{"rendered":"Ways to get and deal with invalid node identifiers in SQL Server Edge references"},"content":{"rendered":"

One of the weirder things about graph tables in SQL Server is the mysterious backend implementation. The values you see when working with these objects look like this: {“type”:”node”,”schema”:”dbo”,”table”:”Node1″,”id”:0}, but when you get errors, you don’t see the json, you see what this represents (two integers for the object_id and unique id in the graph table, as I showed in this blog<\/a>). Since all of this happens in the engine, the values you see in the JSON, you kind of expect would have to correspond to something real. But this is not always the case. There are two ways (that I know of) that this can happen. The first way is when you delete nodes that had edge references but not constraint. The next way is the just insert invalid (or at least, currently invalid data).<\/p>\n

For example, take these two node objects, and 2 edges:<\/p>\n

CREATE TABLE dbo.Node1(Name varchar(20)) AS NODE;\r\nCREATE TABLE dbo.Node2(Name varchar(20)) AS NODE;<\/pre>\n
For one edge table, I will create it to allow any nodes to connect:<\/p>\n
CREATE TABLE dbo.Edge1 AS EDGE;<\/pre>\n
And then another edge, but this one will have 2 edge conditions defined to use cascading deletes (so if either node is deleted, the edge is removed).<\/p>\n
CREATE TABLE dbo.Edge2\r\n(\r\n\tCONSTRAINT EC_Edge2 CONNECTION \r\n                (dbo.Node2 TO dbo.Node2, \r\n                 dbo.Node1 TO dbo.Node2) ON DELETE CASCADE\r\n) AS EDGE;<\/pre>\n
Next I will add a couple of nodes to each node table:<\/p>\n
INSERT INTO dbo.Node1(Name)\r\nVALUES('One'),('Two');\r\n\r\nINSERT INTO dbo.Node2(Name)\r\nVALUES('Buckle'),('Shoe');<\/pre>\n
Take a look at the data that has been created:<\/p>\n
SELECT *\r\nFROM   dbo.Node1;\r\nSELECT *\r\nFROM   dbo.Node2;<\/pre>\n
Each row output has the node_id value (with a unique name amongst all other tables.<\/p>\n
$node_id_1E3D804E50C142CEA8041BF16862E7AC                Name\r\n-------------------------------------------------------- --------------------\r\n{\"type\":\"node\",\"schema\":\"dbo\",\"table\":\"Node1\",\"id\":0}    One\r\n{\"type\":\"node\",\"schema\":\"dbo\",\"table\":\"Node1\",\"id\":1}    Two\r\n$node_id_28039674CFE4498FB0B24CDAD854F145                Name\r\n-------------------------------------------------------- --------------------\r\n{\"type\":\"node\",\"schema\":\"dbo\",\"table\":\"Node2\",\"id\":0}    Buckle\r\n{\"type\":\"node\",\"schema\":\"dbo\",\"table\":\"Node2\",\"id\":1}    Shoe<\/pre>\n
Using those values, we can establish a link in both edges, with the same id values. Edge1:<\/p>\n
INSERT INTO dbo.Edge1($from_id, $to_id)\r\nSELECT (SELECT $node_id FROM dbo.Node2 WHERE name = 'Buckle'),\r\n\t   (SELECT $node_id FROM dbo.Node2 WHERE name = 'Shoe');\r\n\r\nINSERT INTO dbo.Edge1($from_id, $to_id)\r\nSELECT (SELECT $node_id FROM dbo.Node1 WHERE name = 'One'),\r\n\t   (SELECT $node_id FROM dbo.Node2 WHERE name = 'Buckle');<\/pre>\n
Then edge2:<\/p>\n
INSERT INTO dbo.Edge2($from_id, $to_id)\r\nSELECT (SELECT $node_id FROM dbo.Node2 WHERE name = 'Buckle'),\r\n\t   (SELECT $node_id FROM dbo.Node2 WHERE name = 'Shoe');\r\n\r\nINSERT INTO dbo.Edge2($from_id, $to_id)\r\nSELECT (SELECT $node_id FROM dbo.Node1 WHERE name = 'One'),\r\n\t   (SELECT $node_id FROM dbo.Node2 WHERE name = 'Buckle');<\/pre>\n
Looking at the data in Edge1:<\/p>\n
SELECT Node2.Name AS FromNode, LinkedTo.Name AS ToNode\r\nFROM  dbo.Node2,dbo.Edge1,dbo.Node2 AS LinkedTo\r\nWHERE MATCH(Node2-(Edge1)->LinkedTo);<\/pre>\n
This returns:<\/p>\n
FromNode             ToNode\r\n-------------------- --------------------\r\nBuckle               Shoe<\/pre>\n
Now we delete a node:<\/p>\n
DELETE FROM dbo.Node2\r\nWHERE  Node2.Name = 'Buckle';<\/pre>\n
Now re-execute the previous query, and you get a seemingly strange result:<\/p>\n
FromNode             ToNode\r\n-------------------- --------------------\r\nNULL                 Shoe<\/pre>\n
If you look at the data in the tables, it is evident what has occurred:<\/p>\n
SELECT *\r\nFROM   dbo.Node2;\r\n\r\nSELECT *\r\nFROM   dbo.Edge1;<\/pre>\n
This returns:<\/p>\n
$node_id_9163A449BE314C8CAA08E02FF3F1FE3E               Name\r\n------------------------------------------------------- --------------------\r\n{\"type\":\"node\",\"schema\":\"dbo\",\"table\":\"Node2\",\"id\":1}   Shoe\r\n\r\n$edge_id_F8D6E993D86E40A592E94A7E9C08EE99               $from_id_7B55F84E631C4739A4941A6768F1370D              Continued Below\r\n------------------------------------------------------- -------------------------------------------------------\r\n{\"type\":\"edge\",\"schema\":\"dbo\",\"table\":\"Edge1\",\"id\":0}   {\"type\":\"node\",\"schema\":\"dbo\",\"table\":\"Node2\",\"id\":0}  \r\n{\"type\":\"edge\",\"schema\":\"dbo\",\"table\":\"Edge1\",\"id\":1}   {\"type\":\"node\",\"schema\":\"dbo\",\"table\":\"Node1\",\"id\":0}  \r\n            $to_id_C170E534EA6348B488B4927A955C02CD\r\n            ----------------------------------------------------------\r\n            {\"type\":\"node\",\"schema\":\"dbo\",\"table\":\"Node2\",\"id\":1}\r\n            {\"type\":\"node\",\"schema\":\"dbo\",\"table\":\"Node2\",\"id\":0}<\/pre>\n
You can see in the $from_id and $to_id that there is an id:0 that doesn’t exist in the table. If you do this with the other edge, you will see that because of the CASCADE connection, that the edge is removed. Neither of the following query returns data:<\/p>\n
SELECT Node2.Name AS FromNode, LinkedTo.Name AS ToNode\r\nFROM  dbo.Node2,dbo.Edge2,dbo.Node2 AS LinkedTo\r\nWHERE MATCH(Node2-(Edge2)->LinkedTo);\r\n\r\nSELECT *\r\nFROM   dbo.Edge2;<\/pre>\n
Going back to the Edge1 object, we are missing the Buckle node, but if you try to add it back:<\/p>\n
INSERT INTO dbo.Node2(Name)\r\nVALUES('Buckle');<\/pre>\n
But even after you add back the row, the orphaned key value in the $from_id and $to_id values is not added so the following query still returns NULL for the Buckle side of the result:<\/p>\n
SELECT Node2.Name AS FromNode, LinkedTo.Name AS ToNode\r\nFROM  dbo.Node2,dbo.Edge1,dbo.Node2 AS LinkedTo\r\nWHERE MATCH(Node2-(Edge1)->LinkedTo);<\/pre>\n
If you want to add back that data in exactly the form it came as, you have to actually create that value manually by providing the node_id to the insert, using the format that you get from the output of the queries:<\/p>\n
DELETE FROM dbo.Node2 WHERE Name = 'Buckle';\r\n\r\nINSERT INTO dbo.Node2($Node_id, Name)\r\nVALUES ('{\"type\":\"node\",\"schema\":\"dbo\",\"table\":\"Node2\",\"id\":0}','Buckle')<\/pre>\n
Now you see the following:<\/p>\n
SELECT *\r\nFROM   dbo.Node2;<\/pre>\n
This returns:<\/p>\n
$node_id_7AEF81800C7C46808238FA0683232FDA              Name\r\n------------------------------------------------------ -------\r\n{\"type\":\"node\",\"schema\":\"dbo\",\"table\":\"Node2\",\"id\":0}  Buckle\r\n{\"type\":\"node\",\"schema\":\"dbo\",\"table\":\"Node2\",\"id\":1}  Shoe<\/pre>\n
And the MATCH works too:<\/p>\n
SELECT Node2.Name AS FromNode, LinkedTo.Name AS ToNode\r\nFROM  dbo.Node2,dbo.Edge1,dbo.Node2 AS LinkedTo\r\nWHERE MATCH(Node2-(Edge1)->LinkedTo);<\/pre>\n
The row is back:<\/p>\n
FromNode             ToNode\r\n-------------------- --------------------\r\nBuckle               Shoe<\/pre>\n
In a future blog, I will extend this concept of inserting your own nodes and show the value\\limitation of the process.<\/p>\n","protected":false},"excerpt":{"rendered":"
One of the weirder things about graph tables in SQL Server is the mysterious backend implementation. The values you see when working with these objects look like this: {“type”:”node”,”schema”:”dbo”,”table”:”Node1″,”id”:0}, but when you get errors, you don’t see the json, you see what this represents (two integers for the object_id and unique id in the graph……<\/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,1],"tags":[],"coauthors":[19684],"class_list":["post-90238","post","type-post","status-publish","format-standard","hentry","category-blogs","category-uncategorized"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/90238","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=90238"}],"version-history":[{"count":1,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/90238\/revisions"}],"predecessor-version":[{"id":90240,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/90238\/revisions\/90240"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=90238"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=90238"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=90238"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=90238"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}