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