Referential integrity between a graph node and a table in SQL Server

Question

I have a table called Customer with primary key CustomerId.

I have a graph node called CustomerNode. The CustomerNode has CustomerId as one of the columns. I want to establish a referential integrity between these two tables so an accidental deletion of a Customer must not happen. A Customer must be deleted only after the CustomerNode is deleted.

I looked at this documentation, but don't see any reference to how to build relationships between a graph node and a table. SQL Server Management Studio also does not provide a "design" option to do that.

Is it possible to create a relationship between a graph node table and another flat table?

score 2 · Accepted Answer · answered Nov 23 '22 at 05:14

A regular foreign key seems to do what you want. Here's my sample code

use tempdb;
drop table if exists dbo.CustomerNode;
drop table if exists dbo.Customer;

CREATE TABLE dbo.Customer (
    CustomerID INT NOT NULL PRIMARY KEY IDENTITY,
    FamilyName nvarchar(100),
    GivenName nvarchar(100)
);

CREATE TABLE dbo.CustomerNode (
    CustomerID INTEGER PRIMARY KEY,
    CONSTRAINT FK_CustomerNode__Customer
        FOREIGN KEY (CustomerID)
        REFERENCES dbo.Customer(CustomerID)
) AS NODE;
GO

declare @output table (CustomerID int);

insert into dbo.Customer (FamilyName, GivenName)
    output inserted.CustomerID into @output(CustomerID)
    values ('Thul', 'Ben');

insert into dbo.CustomerNode
    (CustomerID)
select CustomerID
from @output;

-- we have rows in the Customer table
select *
from dbo.Customer;

-- we have rows in the CustomerNode table
select *
from dbo.CustomerNode;

-- delete should fail because of foreign key constraint
delete dbo.Customer;

What about edge table? i.e. it should be synchronized with origin table too — ZedZip, Jan 13 '23 at 09:02
Given that RI doesn't seem to mind if a referenced table has been declared as `AS NODE`, I'd suspect that it will likewise be ambivalent to `AS EDGE`. Also, you can get a different kind of RI in that an edge table can have a "connection constraint" on it that restricts the head and tail of the edge to specific tables. That said, while yours is a good question, it was out of scope for the OP's question. — Ben Thul, Jan 13 '23 at 14:11

Referential integrity between a graph node and a table in SQL Server

1 Answers1