參照完整性——“深度”中的間接外鍵

問題

當從多個關係樹創建“子”或“孫子”表時，確保維護“祖父”或“曾祖父”表中的鍵的最佳實踐是什麼。

詳細資訊，因為該問題可能沒有意義

我正在嘗試建立一個數據庫來跟踪我們環境中執行的自動化程序的執行狀態。

一般來說，我們有一個“作業”，它觸發一個或多個“執行檔”，這些“執行檔”可以為一個或多個客戶執行任務。然後我們將有 2 個記錄表，一個跟踪“作業”何時啟動，另一個記錄每個“ExecutableCustomer”實例的成功與失敗狀態。

計劃的簡化模式如下：

當我們將記錄正確到 時JobExecutableCustomerExecutionLog，我想確保與Job.IDvalue 關聯的值與與關聯的JobExecutionLog.ID值匹配。Job.ID``JobExecutableCustomer.ID

通常我會用 aForeign Key但是因為Job.ID不儲存在JobExecutableCustomer,JobExecutableCustomerExecutionLog也不JobExecutionLog。關係是間接的。

例子：

我有 2 個工作，“發送電子郵件”和“發送簡訊”。“發送電子郵件”啟動屬於 1 個客戶的單個執行檔。“發送文本消息”有 2 個執行檔（兩者都為同一個客戶執行）。我想確保當記錄被寫入JobExecutableCustomerExecutionLog“發送電子郵件”時，Job.ID關聯JobExecutableCustomerExecutionLog.JobExecutableCustomerID和JobExecutableCustomerExecutionLog.JobExecutionLogID（在關係上升之後）實際上屬於Job.ID“發送電子郵件”而不是“發送簡訊”。

正如我所看到的，我有兩個選擇：

1. 將值從Job.ID所有子表中推入，並使其成為Foreign Key
2. 讓另一個過程（Trigger或Indexed View）為我確保關係

我個人不喜歡Job.ID在所有其他子表上推送值的想法，所以我傾向於使用 aTrigger或其他東西來處理它。我不知道這是否是我僅有的兩個選擇，或者我是否有能力配置“正常”Foreign Key來遍歷關係。在某種Cascade或其他方面。

我個人不喜歡在所有其他子表上推送 Job.ID 值的想法

為什麼不？顯而易見的解決方案是讓 JobID 成為所有子表上的前導 PK/聚集索引列。它確保通過 JobID 訪問所有這些表的最佳性能。

通常，只要您有一個“標識關係”，也就是“子表”，也就是“弱實體”，子表應該使用複合主鍵，其前導列也是父表的外鍵。像這樣的東西：

create table Parent
(
 ParentId int not null identity,
 constraint pk_Parent
   primary key(ParentId)
)

create table Child
(
 ParentId int not null, 
 ChildId int not null identity, 
 constraint pk_Child 
   primary key (ParentId,ChildId),
 constraint fk_Child_Parent
   foreign key (ParentId) references Parent(ParentId)
   on delete cascade
)
create table GrandChild
(
 ParentId int not null references Parent, 
 ChildId int not null, 
 GrandChildId int not null identity,
 constraint pk_GrandChild 
   primary key (ParentId,ChildId,GrandChildId),
 constraint fk_GrandChild_Child
   foreign key (ParentId) references Parent(ParentId)
   on delete cascade
)

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