1

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

  • 1
    why do you want this? The easiest way to get what you appear to be asking for is to drop the foreign key relationship. – Jasen Jun 8 '18 at 23:02
2

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.

0

Relational integrity is a relationshp between tables, it is not defined by statements.

you could possibly get something a bit like what you are asking for using a check contraint on Test2.Test1Id. but that would block updates to records in Test2 after their parent in Test1 was deleted (unless the updated fixed Test1Id).

another approach could be to add a mirror table that gets a copy of allthe rows in table1 (using triggers) but never gets the deletes. ahd have the relationship from table2 point to the mirror table instead.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.