避免循環/多個更新路徑的首選設計

考慮以下設計：

CREATE TABLE dbo.Farmers
(
   FarmerName varchar(10) NOT NULL
       PRIMARY KEY 
);

CREATE TABLE dbo.FarmEquipment
(
   FarmEquipmentName varchar(10) NOT NULL
       PRIMARY KEY 
   , CreatorFarmer varchar(10) NOT NULL
       FOREIGN KEY REFERENCES dbo.Farmers(FarmerName)
       ON UPDATE CASCADE
       ON DELETE CASCADE
);

CREATE TABLE dbo.Fields
(
   FieldName varchar(10) NOT NULL
   , OwnerFarmer varchar(10) NOT NULL
       FOREIGN KEY REFERENCES dbo.Farmers(FarmerName)
       ON UPDATE CASCADE
       ON DELETE CASCADE
   , FarmEquipment varchar(10) NOT NULL
       FOREIGN KEY REFERENCES dbo.FarmEquipment(FarmEquipmentName)
       ON UPDATE CASCADE
       ON DELETE CASCADE
);

案例：

• 農民喬擁有土地。
• 農夫特德擁有土地。
• Farmer Joe 建造了一台聯合收割機，並將其借給 Farmer Ted，用於 Farmer Ted 的一塊田地。
• 如果 Farmer Joe 將他的名字改為 Farmer Joseph，我希望將其反映在Fields表格中，以便 Farmer Ted 知道誰擁有他一直在使用的聯合收割機。

我意識到這是代理鍵的確切案例，但我試圖確定如果您只使用自然鍵，這將如何工作。

在 SQL Server 中，您實際上無法創建此結構，因為ON UPDATE CASCADEandON UPDATE DELETE子句會dbo.Fields產生以下錯誤消息：

消息 1785，級別 16，狀態 0，第 21

行在表“欄位”上引入 FOREIGN KEY 約束“FK__Fields__FarmEqui__3AD6B8E2”可能會導致循環或多個級​​聯路徑。

指定 ON DELETE NO ACTION 或 ON UPDATE NO ACTION，或修改其他 FOREIGN KEY 約束。

消息 1750，級別 16，狀態 0，行 21

無法創建約束。請參閱以前的錯誤。

這個模型在 SQL Server 中應該如何表達？

我意識到我可以刪除這些ON UPDATE條款，但這並不是重點。我希望更新和刪除級聯。

我看到的解決方案是刪除外鍵約束，並通過觸發器保持關係完整性。

在外部（或“子”）表上，您需要INSERT和UPDATE触發器。如果插入/更新了“外鍵”列，則必須確保值存在於“父”表的主鍵中。

UPDATE在主鍵列的“父”表上，您必須使用舊值找到任何“子”表（FarmEquipment並且Fields都是 的“子” Farmers）中的所有行，並將它們更新為新值價值。

在“父”表上DELETE，您必須檢查“子”表以查看是否有任何行使用有問題的主鍵，然後將它們全部刪除。

使用過以這種方式工作的供應商提供的系統，我不能推薦它。

• 如上所述，您必須為每個外鍵關係維護三個程式碼塊，至少跨越兩個觸發器（至少暫時不討論自引用情況）。
• 綁定到多個“子”表的“父”表需要檢查每個“子”表。雖然每個“子”表的程式碼應該非常相似，但這實際上會使修改變得更加困難，因為很容易做出意在錯誤影響的FarmEquipment更改Fields。（在我提到的實際範例中，至少有一個父表具有至少 20 個“FK”關係——有時在同一個子表中有多個連結，指向不同的欄位。
• 關係的文件不再是內置的。雖然您不能（據我所知）右鍵點擊一個表並找到指向它的所有外鍵關係，但很容易找到一個查詢來從 SQL Server 的結構表中檢索此資訊。當通過觸發器維護關係時，您幾乎必須手動通過觸發器來辨識所有關係，以及精確的定義（“父”表的DELETE觸發器是您可以確定關係是否是CASCADE, NO ACTION, SET NULL,或SET DEFAULT- 具有外鍵約束，這可以從“子”表中看出）。

有一些變通方法（觸發器程式碼確實以可查詢的形式存在），但它們要求開發人員遵循有關命名約定、查詢構造甚至可能是程式碼格式的嚴格規則。即使一個人正在維護程式碼，並使用模板來設置所有內容，但在進行更改以及發現和修復錯誤時保持所有內容變得更加困難。

我使用的系統使用這種方法執行了 10 多年。他們在沒有有效外鍵的環境中啟動了他們的應用程序。但即使他們已經到了將外鍵用於新表的地步，並開始盡可能將它們放在現有表上。只是使用它並不容易。

我懷疑這不符合您的標準；但是，由於它是一個可行的解決方案，我認為它至少值得介紹。

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