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?

Answer 1

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;