如何為其他一些列的唯一組合只強制一個 NULL 值？

情況就是這樣，我有一張像這樣的表A

CREATE TABLE A
(
   id         UUID    NOT NULL PRIMARY KEY,
   B_id       bigint  NOT NULL,
   C_id       bigint  NOT NULL,
   some_value TIME,
   enabled    BOOLEAN NOT NULL,

   CONSTRAINT A_B_id_fkey FOREIGN KEY (B_id) REFERENCES B (id),
   CONSTRAINT A_C_id_fkey FOREIGN KEY (C_id) REFERENCES C (id)
);

而且我希望只有一個空值來表示some_value何時enabled為假。另外，我想確保這個 null 適用於所有外鍵。所以我可以有 x*y 空值，但每對（x，y）只有一個。我做了這樣的事情：

CREATE UNIQUE INDEX CONCURRENTLY idx_A_some_value
   ON A (B_id, C_id, (some_value IS NULL), (enabled IS FALSE))
   WHERE (some_value IS NULL);

CREATE UNIQUE INDEX CONCURRENTLY idx_A_some_value
   ON A (B_id, C_id, (some_value IS NOT NULL), (enabled IS TRUE))
   WHERE (some_value IS NOT NULL);

看起來正確嗎？有沒有更好的辦法？此外，就我而言，性能很重要。謝謝

編輯：

我修改了條件並做了一些更簡單的事情：

CREATE UNIQUE INDEX CONCURRENTLY idx_un_A
   ON A (B_id, C_type_id);

ALTER TABLE A
   ADD CONSTRAINT some_value_null_not_enabled
       CHECK ( (enabled IS FALSE AND some_value IS NULL) OR
               (enabled IS TRUE AND some_value IS NOT NULL));

好點嗎？

從這個約束：

CHECK ( (enabled IS FALSE AND some_value IS NULL) OR
           (enabled IS TRUE AND some_value IS NOT NULL));

…“啟用”列是多餘的，僅表示“some_value IS NOT NULL”。如果在查詢中使用它，則可以將其替換為生成的列，或者簡單地從表中刪除。

CREATE TABLE A
(
   id         INT NOT NULL PRIMARY KEY,
   B_id       INT NOT NULL,
   C_id       INT NOT NULL,
   t          TIME
--    ,enabled    BOOL NOT NULL GENERATED ALWAYS AS (t IS NOT NULL)
);

因此，對於每個 (B_id,C_id)，您最多希望在 t 列中有一個空值。注意“啟用”消失了，這簡化了情況。你可以這樣做：

CREATE UNIQUE INDEX idx_A_null_t ON (B_id,C_id) WHERE t IS NULL;

現在，如果“啟用”所做的不僅僅是複制“t 為空”，那麼您需要這兩列。如果存在 t 不為 null 且 enabled 為 false 的行，就會出現這種情況。如果您想要的是：對於每個 (B_id,C_id) 最多一行啟用 = false 並且 t 為空，那麼：

CREATE UNIQUE INDEX idx_A_null_t ON (B_id,C_id) WHERE t IS NULL AND NOT enabled;

…並添加一個約束，如果 enabled 為真，則 t 不能為空。

請注意，這似乎與問題無關：

CREATE UNIQUE INDEX CONCURRENTLY idx_A_some_value
ON A (B_id, C_id, (some_value IS NOT NULL), (enabled IS TRUE))
WHERE (some_value IS NOT NULL);

因為這意味著對於 (B_id,C_id) 的每個值最多可以有一行 t 不為空，這聽起來不像你想要的。

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