Sql-server – Alternative to second column for tracking a FOREIGN KEY pre-deletion

database-designforeign keysql serversql server 2014trigger

Let's say I have a table A, that has one column: just an ID. This is obviously the primary key.

Next, I have a second table, which has an ID for table A, and then a second ID. The primary key is on it's own ID. Now I want to be able to delete rows from A, but not lose the foreign key in B.

There's no ON DELETE SKIP INTEGRITY or anything, so I have to use a trigger and second column to retain it:

CREATE TABLE Test1 
(
    Id INT NOT NULL,
    CONSTRAINT PK_Test1 PRIMARY KEY (Id)
)

CREATE TABLE Test2
(
    Test1Id INT NULL,
    OldTest1Id INT NULL,
    Id INT NOT NULL,
    CONSTRAINT PK_Test2 PRIMARY KEY (Id),
    CONSTRAINT FK_Test2_Test1Id_Test1_Id FOREIGN KEY (Test1Id) REFERENCES Test1 (Id) ON DELETE SET NULL
)
GO

CREATE TRIGGER Test2_OldTest1Id ON Test2
AFTER INSERT, UPDATE
AS
UPDATE Test2
SET OldTest1Id = Test1Id
WHERE
    EXISTS(SELECT 1 FROM inserted WHERE inserted.Id = Test2.Id)
    AND Test1Id IS NOT NULL
GO

Are there any alternatives to this that don't require a TRIGGER or second column? Is there any way I can force referential-integrity for all but DELETE statements? I don't care about tracking what it is now vs. what it was, I just want to retain that FOREIGN KEY value forever, even if it's deleted from the referenced table.

I have a sample db<>fiddle to illustrate:

https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=ba02ec43fff23231cb5715aaa265c1e5

Best Answer

I've worked with a SQL Server DB where the relationship between two tables was maintained by triggers instead of actual foreign keys (without the need for the second column).

Basically, in the "child" table:

  • on INSERT, check to make sure the inserted value in the "FK" column exists in the "parent" table;
  • on UPDATE, check to make sure any updated value in the "FK" column exists in the parent table.

In the "parent" table (using an AFTER trigger or equivalent):

  • on UPDATE, if the "PK" value is changed, take whatever action is appropriate if there are rows in the "child" table using the PK (change the "FK" column to match; prevent the update if there are "children" already using the current value; do nothing; etc.)
  • on DELETE, take whatever action is appropriate if there are rows in the "child" table using the PK (prevent the deletion; set the value to NULL; or, in your case, do absolutely nothing).

Since the DB is not maintaining the relationship, you need to make sure you handle each of the possibilities above properly. It'll need to be documented as a known "gotcha" of the DB design, something that new devs get cued in on quickly, so they don't make assumptions regarding it.

Also, remember that disabling triggers also disables this "foreign key" relationship (unlike a true FK).

I'm not necessarily saying this would be better than your history column solution (which has its own maintenance hassles), but it is an option.

Related Question