Sql-Server

帶常量的外鍵

  • June 11, 2021

假設我有一個表 A,它有兩列:一列是 的 ID ThingA,一列是 的 ID ThingB。主鍵是(ThingA, ThingB).

接下來,我有第二個表,但這次它僅限於表A中具有ThingB = 3. 主鍵是ThingA, 因為ThingB是常數 3。

最初,我以為我可以簡單地:

FOREIGN KEY (ThingA, 3) REFERENCES A(ThingA, ThingB)

但我了解到情況並非如此,我必須為以下內容創建一個列ThingB

ThingB INT NOT NULL DEFAULT(3) CHECK(ThingB = 3)

然後,

FOREIGN KEY (ThingA, ThingB) REFERENCES A (ThingA, ThingB)

是否有不需要額外列的替代方法,或者DEFAULT + CHECK?一種替代方法是持久化計算列,但我也討厭這個想法,因為它基本上是一種作弊,並且仍然添加了一個帶有物理儲存的新列。雖然它自己,INT不會很大,有幾百萬行需要跨多個表,我寧願不維護額外的列。

這是用於說明情況的範例 DDL:

CREATE TABLE Test1
(
   ThingA INT NOT NULL,
   ThingB INT NOT NULL,
   PRIMARY KEY (ThingA, ThingB)
);

CREATE TABLE Test2
(
   ThingAVal INT NOT NULL,
   ThingBVal INT NOT NULL DEFAULT(3) CHECK(ThingBVal = 3),
   Val INT NOT NULL,
   FOREIGN KEY (ThingAVal, ThingBVal) REFERENCES Test1 (ThingA, ThingB)
);

我創建了一個 db<>fiddle 來展示我的(目前)解決方案:

如果答案是“否”,我會接受,但我很好奇是否還有其他選擇。

您說您不想向需要以這種方式連結回Test1 的許多表添加額外的列(即在ThingA 上,3)。

如果 ThingB 為 3,則向 TestA 添加一個顯示值 ThingA 的持久計算列如何,否則為 null?

然後,您的外鍵僅根據引用表中的 ThingA 引用新列。

alter table Test1 add SpecialThingA as
   (case ThingB when 3 then ThingA else null end) persisted;

FOREIGN KEY (ThingA) REFERENCES Test1 (SpecialThingA)

換句話說 - Test1 上的一個新列,而不是 Test2 上的一個新列(保存“3”)和 Test3,以及….

我認為答案是否定的。

根據聯機叢書,外鍵必須在引用表中引用唯一約束

似乎解決方案可能是過濾的唯一索引,但這不算作約束。

你可以在這裡查看:

SELECT 
   i.name
   , i.is_primary_key          -- can be referenced by FK
   , i.is_unique_constraint    -- can be referenced by FK
   , i.is_unique               -- cannot be referenced by FK
FROM sys.indexes AS i 

我不認為你可以在不複製數據的情況下安全地做到這一點 - 你的解決方案似乎是最好的(參考表上的 CHECK 約束)

基於 Test1 和 ThingB = 3 過濾器的索引視圖可能也可以工作,但您將保留不同的列。

我通常避免使用觸發器,因為它們不會強制引用完整性以及其他約束,但這可能是要走的路。

引用自:https://dba.stackexchange.com/questions/209038