用於跟踪 FOREIGN KEY 預刪除的第二列的替代方法

假設我有一個表 A，它有一列：只有一個 ID。這顯然是主鍵。

接下來，我有第二個表，它有一個表 A 的 ID，然後是第二個 ID。主鍵在它自己的 ID 上。現在我希望能夠從 A 中刪除行，但不會失去 B 中的外鍵。

沒有ON DELETE SKIP INTEGRITY或沒有任何東西，所以我必須使用觸發器和第二列來保留它：

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

有沒有不需要一TRIGGER列或第二列的替代方法？有什麼方法可以強制除DELETE語句之外的所有引用完整性嗎？我不在乎跟踪它現在是什麼與過去是什麼，我只想FOREIGN KEY永遠保留該值，即使它已從引用的表中刪除。

我有一個範例 db<>fiddle 來說明：

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

我使用過 SQL Server DB，其中兩個表之間的關係由觸發器而不是實際的外鍵維護（不需要第二列）。

基本上，在“子”表中：

• on INSERT，檢查“FK”列中插入的值是否存在於“父”表中；
• on UPDATE，檢查以確保“FK”列中的任何更新值都存在於父表中。

在“父”表中（使用AFTER觸發器或等效項）：

• on UPDATE，如果“PK”值發生更改，如果“子”表中有行使用 PK，則採取任何適當的操作（更改“FK”列以匹配；如果有“子”已經使用，則阻止更新目前值；什麼都不做；等等）
• on DELETE，如果使用 PK 的“子”表中有行，則採取任何適當的操作（防止刪除；將值設置為 NULL；或者，在您的情況下，絕對不執行任何操作）。

由於數據庫沒有維護這種關係，因此您需要確保正確處理上述每種可能性。它需要被記錄為數據庫設計的已知“陷阱”，新開發人員很快就會得到提示，因此他們不會對此做出假設。

另外，請記住，禁用觸發器也會禁用這種“外鍵”關係（與真正的 FK 不同）。

我不一定說這會比您的歷史列解決方案（它有自己的維護麻煩）更好，但它是一種選擇。

關係完整性是表之間的關係，它不是由語句定義的。

您可能會得到一些類似於您要求在 Test2.Test1Id 上使用檢查約束的東西。但這會在Test2其父 inTest1被刪除後阻止對記錄的更新（除非更新的 fixed Test1Id）。

另一種方法可能是添加一個鏡像表，該表獲取 table1 中所有行的副本（使用觸發器）但永遠不會刪除。ahd 有從 table2 指向鏡像表的關係。

引用自：https://dba.stackexchange.com/questions/209141