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

For example, take these two node objects, and 2 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 edge conditions defined to use cascading deletes (so if either node is deleted, the edge is removed).

Next I will add a couple of nodes to each node table:

Take a look at the data that has been created:

Each row output has the node_id value (with a unique name amongst all other tables.

Using those values, we can establish a link in both edges, with the same id values. Edge1:

Then edge2:

Looking at the data in Edge1:

This returns:

Now we delete a node:

Now re-execute the previous query, and you get a seemingly strange result:

If you look at the data in the tables, it is evident what has occurred:

This returns:

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:

Going back to the Edge1 object, we are missing the Buckle node, but if you try to add it back:

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:

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:

Now you see the following:

This returns:

And the MATCH works too:

The row is back:

In a future blog, I will extend this concept of inserting your own nodes and show the value\limitation of the process.