處理一對可以“邏輯上”成為身份一部分的可為空的外鍵

也許我會以錯誤的方式解決這個問題，但這裡就是這樣。一點背景：

我正在設計一個系統，使用者將在其中選擇具有指定順序的欄位。這些欄位與配置文件相關聯。這些欄位是從 2 個不同的表中選擇的，一個帶有系統欄位，一個帶有自定義欄位。這些欄位將儲存在一個表中，但最初的設計實際上並沒有定義欄位表和映射表之間的任何模式關係。

我想實際正確地映射 2 個不同的表，但我遇到瞭如何定義映射表的問題。這是原始設計，對我來說似乎是“合乎邏輯的”。

我如何建構這個映射表？我覺得外鍵應該用作主鍵，但顯然每一行都有一個可為空的CustomFieldId或FieldId，這使得這不可能。

我是否放棄將外鍵作為主鍵並在表中添加唯一性約束？

您在這裡缺少一個額外的關係（表格）。

你所擁有的是一個多態關聯，其中你有一個 a 的概念Field，它可以是 aSystemField或 a CustomField。有很多方法可以做到這一點，例如你可以只使用一個bit標誌，但一種常見的是將它們分成不同的表，並使用Id基表的 和 aType作為子表的 PK，並將外鍵返回到Field.

現在您可以簡單地ProfileFields參考基表，無需擔心 aField是哪種類型。

那麼你需要的是下表（虛擬碼）：

CREATE TABLE Field (
 Id int IDENTITY,
 FieldType char(1) CHECK (FieldType IN ('S', 'C')),
 PRIMARY KEY (Id, FieldType),
...)
-- this represents any type of field

CREATE TABLE SystemField (
 Id int,   -- no IDENTITY
 FieldType char(1) CHECK (FieldType = 'S'),
 PRIMARY KEY (Id, FieldType),
 FOREIGN KEY (Id, FieldType) REFERENCES Field (Id, FieldType),
...)

CREATE TABLE CustomField (
 Id int,   -- no IDENTITY
 FieldType char(1) CHECK (FieldType = 'C'),
 PRIMARY KEY (Id, FieldType),
 FOREIGN KEY (Id, FieldType) REFERENCES Field (Id, FieldType),
...)

CREATE TABLE Profile ( .... )

CREATE TABLE ProfileFields (
 ProfileId int,
 FieldId int,
 FieldType char(1),
 PRIMARY KEY (ProfileId, FieldId, FieldType),
 FOREIGN KEY (ProfileId) REFERENCES Profile (Id),
 FOREIGN KEY (FieldId, FieldType) REFERENCES Field (Id, FieldType),
...)

注意ProfileFields不引用兩個子表。

您可以通過首先添加 to 來添加欄位Field，然後獲取其IDENTITY值並將其添加到相關表中：

INSERT Field (FieldType, ...)
VALUES ('S', ... );

INSERT SystemField (Id, FieldType, ...)
VALUES (SCOPE_IDENTITY(), 'S', ... );

您可能會從中刪除FieldType，ProfileFields但隨後您需要Id在基Field表中添加第二個唯一鍵。

您也可以將CHECK約束更改為查找表，但我個人不明白這一點。

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