跨表中所有數據的複雜約束

我們有一個表來記錄系統上發生的處理，我們需要確保只有一行具有“處理中”狀態。

我想確保結果始終為零或一：

select count(id) from jobs where status in ('P', 'G');

我們正在使用顯式事務，因此理想情況下，此檢查將在送出時進行，如果不變數不成立，則中止事務。處理任何引發偶爾錯誤的異常處理對我們來說比突然結束一個以上的“正在進行中”的工作要容易得多。

該解決方案只需要與 Postgres 一起使用，因此我們很樂意為此採用非標準解決方案。我們目前使用的是 8.4，但如果有任何不同，我們將在某個時候升級到 9.x。

用 Postgres 9.1 & 9.2測試。其中大部分也應該適用於 8.4。

簡單案例

如果沒有中間狀態在單個事務過程中違反約束，則**常量值上的****部分 UNIQUE 索引**可以完成這項工作。鑑於此測試案例：

CREATE TEMP TABLE jobs(jobs_id int primary key, status text);
INSERT INTO jobs (jobs_id, status)
VALUES (1, 'A'), (2, 'B'), (3, 'C'), (4, 'G');

使用這個索引：

CREATE UNIQUE INDEX jobs_status_uni_idx ON jobs ((TRUE))
WHERE status in ('P', 'G');

請注意 value 周圍的附加括號TRUE。

測試（一次一行）：

INSERT INTO jobs (jobs_id, status) VALUES (5, 'G'); -- fails
INSERT INTO jobs (jobs_id, status) VALUES (5, 'P'); -- fails

DELETE FROM jobs WHERE status = 'G';

INSERT INTO jobs (jobs_id, status) VALUES (5, 'P'); -- succeeds
INSERT INTO jobs (jobs_id, status) VALUES (6, 'G'); -- fails

SQL小提琴。

更多解釋的相關答案：

PostgreSQL multi-column unique constraint and NULL values

DEFERRED帶約束的高級案例

理想情況下，此檢查將在送出時進行

部分唯一索引不能延遲，並且總是立即檢查。因此，繼續上面的範例，這將失敗：

BEGIN;
INSERT INTO jobs (jobs_id, status) VALUES (6, 'G');  -- fails immediately!
DELETE FROM jobs WHERE status = 'P';
COMMIT;

範例是顯式事務處理。您的客戶可能有自動交易處理（autocommit開或關）。

如果你需要這個工作，你需要一個**DEFERRABLE**約束是INITIALLY DEFERRED或SET CONSTRAINTS ALL | 事務中的名稱 DEFERRED 。

我在這裡引用手冊

非延遲唯一性約束

當UNIQUEorPRIMARY KEY約束不可延遲時，PostgreSQL 會在插入或修改行時立即檢查唯一性。SQL 標准說，唯一性應該只在語句的末尾強制執行。例如，當單個命令更新多個鍵值時，這會有所不同。要獲得符合標準的行為，請將約束聲明為DEFERRABLE但不延遲（即INITIALLY IMMEDIATE）。請注意，這可能比立即唯一性檢查要慢得多。

手冊中有關索引唯一性檢查的更多詳細資訊。

但是，aUNIQUE CONSTRAINT只能為列定義，不能為表達式定義。

為此目的添加一個冗餘列，並使其與觸發器保持同步。像這樣：

ALTER TABLE jobs ADD COLUMN status_uni boolean;
UPDATE jobs set status_uni = TRUE WHERE status in ('P', 'G');  -- rest stays NULL

ALTER TABLE jobs ADD CONSTRAINT jobs_status_uni
UNIQUE(status_uni) **DEFERRABLE INITIALLY DEFERRED**;

額外的列很便宜，因為它充滿了 NULL，現有的NULL 點陣圖通常應該吞下它，而不需要額外的物理磁碟空間。

NULL值不違反每個定義的唯一約束。

創建觸發器以始終保持列最新：

CREATE OR REPLACE FUNCTION trg_jobs_status()
 RETURNS trigger AS
$func$
BEGIN
NEW.status_uni = (NEW.status IN ('G', 'P') OR NULL);
RETURN NEW;
END
$func$  LANGUAGE plpgsql;

對於所有插入：

CREATE TRIGGER insbef
BEFORE INSERT ON jobs
FOR EACH ROW EXECUTE PROCEDURE trg_jobs_status();

要優化，僅針對相關更新：

CREATE TRIGGER upbef
BEFORE UPDATE OF status, status_uni ON jobs
FOR EACH ROW EXECUTE PROCEDURE trg_jobs_status();

測試

現在您的交易通過，只要狀態在最後是一致的：

BEGIN;
-- SET CONSTRAINTS jobs_status_uni IMMEDIATE;  -- only for INITIALLY IMMEDIATE
INSERT INTO jobs (jobs_id, status) VALUES (6, 'G');  -- check deferred
DELETE FROM jobs WHERE status = 'P';
COMMIT;  -- succeeds!

SQL 小提琴。

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