Translating Index/Key Errors from Graph Tables in SQL Server

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:

CREATE TABLE dbo.Node(Name varchar(20)) AS NODE;
CREATE TABLE dbo.Edge AS EDGE;

ALTER TABLE dbo.Edge ADD CONSTRAINT AKEdge UNIQUE($from_id, $to_id);

INSERT INTO dbo.Node (Name) VALUES ('Fred');
INSERT INTO dbo.Node (Name) VALUES ('Barney');

INSERT INTO dbo.Edge ($From_id, $to_id)
SELECT  (SELECT $node_id FROM dbo.Node WHERE name = 'Fred'),
                (SELECT $node_id FROM dbo.Node WHERE name = 'Barney');

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

INSERT INTO dbo.Edge ($From_id, $to_id)
SELECT  (SELECT $node_id FROM dbo.Node WHERE name = 'Fred'),
                (SELECT $node_id FROM dbo.Node WHERE name = 'Barney')

This causes the following error message:

Msg 2627, Level 14, State 1, Line 14
Violation of UNIQUE KEY constraint 'AKEdge'. Cannot insert duplicate key 
in object 'dbo.Edge'. The duplicate key value is (455672671, 0, 455672671, 1).

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

SELECT $node_id FROM dbo.Node WHERE name = 'Fred';

This returns:

$node_id_F1ECB5498FC747CFBC24EF390EBCBCC9
---------------------------------------------------------
{"type":"node","schema":"dbo","table":"Node","id":0}

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:

SELECT OBJECT_SCHEMA_NAME(object_id) AS schema_name, name AS ObjectName
FROM   sys.tables
WHERE  tables.object_id = 455672671

This returns:

schema_name      ObjectName
---------------- --------------------------
dbo              Node

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

CREATE OR ALTER PROCEDURE Tools.GraphDB$LookupItem
(
        @ObjectId int,
        @Id int 
)
AS
BEGIN
        SET NOCOUNT ON;
        DECLARE @SchemaName sysname = OBJECT_SCHEMA_NAME(@ObjectId),
                    @TableName sysname = OBJECT_NAME(@ObjectId),
                @SQLStatement nvarchar(MAX)
        SET @SQLStatement = CONCAT('SELECT * FROM ', 
            QUOTENAME(@SchemaName),'.',QUOTENAME(@TableName),
            ' WHERE JSON_VALUE(CAST($node_id AS nvarchar(1000)),''$.id'') = ',@Id)
        EXECUTE (@SQLStatement)
END;

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:

EXEC Tools.GraphDB$LookupItem 455672671, 0;
EXEC Tools.GraphDB$LookupItem 455672671, 1;

This returns:

$node_id_F1ECB5498FC747CFBC24EF390EBCBCC9                Name
-------------------------------------------------------- --------------------
{"type":"node","schema":"dbo","table":"Node","id":0}     Fred

$node_id_F1ECB5498FC747CFBC24EF390EBCBCC9                Name
-------------------------------------------------------- --------------------
{"type":"node","schema":"dbo","table":"Node","id":1}     Barney

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.

 

The post Translating Index/Key Errors from Graph Tables in SQL Server appeared first on Simple Talk.

from Simple Talk https://ift.tt/2MTNAMM
via