僅當第三列為 NOT NULL 時才進行兩列外鍵約束

給定下表：

CREATE TABLE verified_name (
 id               SERIAL PRIMARY KEY,
 name             TEXT NOT NULL,
 email            TEXT NOT NULL,
 UNIQUE (name, email)
);

CREATE TABLE address (
 id               SERIAL PRIMARY KEY,
 name             TEXT NOT NULL,
 email            TEXT NOT NULL,
 verified_name_id INTEGER NULL REFERENCES verified_name(id)
);

如何添加一個額外的約束，即當address.verified_name_id不是 NULL 時，nameandemail上的欄位address必須與 referenced 上的那些匹配verified_name？

我嘗試將以下內容添加到address：

FOREIGN KEY (name, email) REFERENCES verified_name(name, email)

…但即使verified_name_id是 NULL，也會應用該約束。

我正在尋找類似於帶有子句的部分索引WHERE verified_name_id IS NOT NULL語法的東西，但只是將這樣的子句附加到FOREIGN KEY約束是行不通的。

目前不受歡迎的解決方案：

我可以將以下約束添加到verified_name：

UNIQUE (name, email),
UNIQUE (id, name, email)

以及以下約束address：

FOREIGN KEY (verified_name_id, name, email) REFERENCES verified_name(id, name, email)

verified_name…但這會產生一個我不希望擁有的額外約束（這是一個有效的邏輯約束，但它也是多餘的，並且對性能有輕微的影響）。

適當的解決方案

問題的核心是數據模型。在規範化模式中，您不會儲存name和email冗餘。可能看起來像這樣：

CREATE TABLE name (
 name_id          SERIAL PRIMARY KEY,
 name             TEXT NOT NULL,
 email            TEXT NOT NULL,
 verified         BOOLEAN NOT NULL DEFAULT FALSE,
 UNIQUE (name, email)
);

CREATE TABLE address (
 address_id       SERIAL PRIMARY KEY,
 name_id          INT REFERENCES name(name_id)
 ...
);

如果應該允許尚未驗證的名稱打破 UNIQUE 約束，您可以用部分 UNIQUE INDEX替換它（就像您的想法一樣）：

CREATE UNIQUE INDEX name_verified_idx ON name(name, email) WHERE verified;

使用你所擁有的

雖然堅持您不幸的設計，但您已經發現自己的解決方案完全符合您的要求。FOREIGN KEY具有預設MATCH SIMPLE行為的A匹配

帶有子句的部分索引語法，例如WHERE verified_name_id IS NOT NULL.

引用手冊：

MATCH SIMPLE允許任何外鍵列為空；如果其中任何一個為空，則該行不需要在引用的表中具有匹配項。

一個（不太可靠且更昂貴）的替代方法是在 INSERT / UPDATE 中address觸發和在 INSERT / UPDATE / DELETE 中觸發verified_name。

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