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.<\/p>\n
For example, take the following set of tables:<\/p>\n
CREATE TABLE dbo.Node(Name varchar(20)) AS NODE;\r\nCREATE TABLE dbo.Edge AS EDGE;\r\n\r\nALTER TABLE dbo.Edge ADD CONSTRAINT AKEdge UNIQUE($from_id, $to_id);\r\n\r\nINSERT INTO dbo.Node (Name) VALUES ('Fred');\r\nINSERT INTO dbo.Node (Name) VALUES ('Barney');\r\n\r\nINSERT INTO dbo.Edge ($From_id, $to_id)\r\nSELECT (SELECT $node_id FROM dbo.Node WHERE name = 'Fred'),\r\n\t\t(SELECT $node_id FROM dbo.Node WHERE name = 'Barney');<\/pre>\nNow, insert a duplicate node using the same code as the previous:<\/p>\n
INSERT INTO dbo.Edge ($From_id, $to_id)\r\nSELECT (SELECT $node_id FROM dbo.Node WHERE name = 'Fred'),\r\n\t\t(SELECT $node_id FROM dbo.Node WHERE name = 'Barney')<\/pre>\nThis causes the following error message:<\/p>\n
Msg 2627, Level 14, State 1, Line 14\r\nViolation of UNIQUE KEY constraint 'AKEdge'. Cannot insert duplicate key \r\nin object 'dbo.Edge'. The duplicate key value is (455672671, 0, 455672671, 1).<\/pre>\nSo what is this: (455672671, 0, 455672671, 1)? If you look at the $node_id value from the following query:<\/p>\n
SELECT $node_id FROM dbo.Node WHERE name = 'Fred';<\/pre>\nThis returns:<\/p>\n
$node_id_F1ECB5498FC747CFBC24EF390EBCBCC9\r\n---------------------------------------------------------\r\n{\"type\":\"node\",\"schema\":\"dbo\",\"table\":\"Node\",\"id\":0}<\/pre>\nThe 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.<\/p>\n
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.)<\/p>\n
To see this, execute:<\/p>\n
SELECT OBJECT_SCHEMA_NAME(object_id) AS schema_name, name AS ObjectName\r\nFROM sys.tables\r\nWHERE tables.object_id = 455672671<\/pre>\nThis returns:<\/p>\n
schema_name ObjectName\r\n---------------- --------------------------\r\ndbo Node<\/pre>\n