Translating Index/Key Errors from Graph Tables in SQL Server

Comments 0

Share to social media

As I am working with graph tables, I find some quirks exist that make them interesting to work with. One of them is how the values show up in some error messages (in particular from unique indexes). Instead of the details you expect\hope (Something like the JSON value you find in the id columns in the edge and node tables like: {“type”:”node”,”schema”:”dbo”,”table”:”Node”,”id”:0}), you get a pair of numbers that you need to decode.

For example, take the following set of tables:

Now, insert a duplicate node using the same code as the previous:

This causes the following error message:

So what is this: (455672671, 0, 455672671, 1)? If you look at the $node_id value from the following query:

This returns:

The 0 for the id maps to the 0 in the error message (455672671, 0, 455672671, 1), and if you check the row for Barney, you will see that it has a 1 for the id.

But what about the other number? This is the object_id for the table. You can see in this sample data it is duplicated but they could be different, but that value can be different (and will be for things like edge constraints where you are disallowing connection from two different node types in an edge.)

To see this, execute:

This returns:

To make this whole process a little easier, I created the following function (download here: https://github.com/drsqlgithub/DRSQL_ORG-Uploads/blob/main/Tools/ in a file named GraphError.sql):

Using this, you can simply paste the values from the error message and execute the stored procedure to see the row that is offending. Execute this next query and get the two rows that represent the errored row, without knowing what table the object is from:

This returns:

I kept the code simple and just returned all columns, but it could easily be extended for whatever you need with a few additional tables of metadata…With a little work you could use the metadata from the objects related to the errored object and produce cleaner output… Something I may attempt later.