在數據庫中強制執行“至少一個”或“恰好一個”的約束

假設我們有使用者，每個使用者可以有多個電子郵件地址

CREATE TABLE emails (
   user_id integer,
   email_address text,
   is_active boolean
)

一些範例行

user_id | email_address | is_active
1       | foo@bar.com   | t
1       | baz@bar.com   | f
1       | bar@foo.com   | f
2       | ccc@ddd.com   | t

我想強制每個使用者只有一個活動地址。我怎樣才能在 Postgres 中做到這一點？我可以這樣做：

CREATE UNIQUE INDEX "user_email" ON emails(user_id) WHERE is_active=true;

這可以防止使用者擁有多個活動地址，但我相信不會防止他們的所有地址都被設置為假。

如果可能的話，我寧願避免使用觸發器或 pl/pgsql 腳本，因為我們目前沒有這些腳本，而且很難設置。但如果是這樣的話，我會很高興知道“唯一的方法是使用觸發器或 pl/pgsql”。

您根本不需要觸發器或 PL/pgSQL。

你甚至不需要 DEFERRABLE約束。

而且您不需要冗餘地儲存任何資訊。

在表中包含活動電子郵件的 ID users，從而產生相互引用。有人可能認為我們需要一個DEFERRABLE約束來解決插入使用者和他的活動電子郵件的先有雞還是先有蛋的問題，但是使用數據修改 CTE 我們甚至不需要它。

這會始終強制每個使用者只發送一封活動電子郵件：

CREATE TABLE users (
 user_id  serial PRIMARY KEY
, username text NOT NULL
, email_id int NOT NULL  -- FK to active email, constraint added below
);

CREATE TABLE email (
 email_id serial PRIMARY KEY
, user_id  int NOT NULL REFERENCES users ON DELETE CASCADE ON UPDATE CASCADE 
, email    text NOT NULL
, CONSTRAINT email_fk_uni UNIQUE(user_id, email_id)  -- for FK constraint below
);
   
ALTER TABLE users ADD CONSTRAINT active_email_fkey
FOREIGN KEY (user_id, email_id) REFERENCES email(user_id, email_id);

刪除NOT NULL約束users.email_id以使其“最多一封活動電子郵件”。（您仍然可以為每個使用者儲存多封電子郵件，但沒有一個是“活動的”。）

旁白：如果(user_id, email)是唯一的（可以假設），您可以email直接使用而不是email_id…

您可以active_email_fkey DEFERRABLE允許更多的餘地（在同一事務的不同命令中插入使用者和電子郵件），但這不是必需**的。

我將約束user_id放在首位以優化索引覆蓋率。細節：UNIQUE``email_fk_uni

• 複合索引是否也適用於第一個欄位的查詢？

可選視圖：

CREATE VIEW user_with_active_email AS
SELECT * FROM users JOIN email USING (user_id, email_id);

以下是使用活動電子郵件插入新使用者的方法（根據需要）：

WITH new_data(username, email) AS (
  VALUES
     ('usr1', 'abc@d.com')   -- new users with *1* active email
   , ('usr2', 'def3@d.com')
   , ('usr3', 'ghi1@d.com')
  )
, u AS (
  INSERT INTO users(username, email_id)
  SELECT n.username, nextval('email_email_id_seq'::regclass)
  FROM   new_data n
  RETURNING *
  )
INSERT INTO email(email_id, user_id, email)
SELECT u.email_id, u.user_id, n.email
FROM   u
JOIN   new_data n USING (username);

具體的困難是我們既沒有user_id也沒有email_id開始。兩者都是各自提供的序列號SEQUENCE。它不能用一個RETURNING子句來解決（另一個先有雞還是先有蛋的問題）。該解決方案nextval()在下面的連結答案中有詳細說明。

如果您不知道serial列的附加序列的名稱，email.email_id您可以替換：

nextval('email_email_id_seq'::regclass)

和

nextval(pg_get_serial_sequence('email', 'email_id'))

以下是添加新“活動”電子郵件的方法：

WITH e AS (
  INSERT INTO email (user_id, email)
  VALUES  (3, 'new_active@d.com')
  RETURNING *
  )
UPDATE users u
SET    email_id = e.email_id
FROM   e
WHERE  u.user_id = e.user_id;

db<>fiddle here

舊sqlfiddle

如果一些頭腦簡單的 ORM 不夠聰明，無法應對這種情況，您可以將 SQL 命令封裝在伺服器端函式中。

密切相關，並有充分的解釋：

• 如何處理相互遞歸的插入
• SQLAlchemy 中的複雜外鍵約束

還有相關的：

• 強制“兩個表之外”的約束

關於DEFERRABLE約束：

• 約束定義 DEFERRABLE INITIALLY IMMEDIATE 仍然是 DEFERRED？

關於nextval()和pg_get_serial_sequence()：

• PostgreSQL 選擇主鍵為“serial”或“bigserial”
• PostgreSQL中主鍵的重複行

如果您可以向表中添加一列，則以下方案幾乎可以工作：

CREATE TABLE emails 
(
   UserID integer NOT NULL,
   EmailAddress varchar(254) NOT NULL,
   IsActive boolean NOT NULL,

   -- New column
   ActiveAddress varchar(254) NOT NULL,

   -- Obvious PK
   CONSTRAINT PK_emails_UserID_EmailAddress
       PRIMARY KEY (UserID, EmailAddress),

   -- Validate that the active address row exists
   CONSTRAINT FK_emails_ActiveAddressExists
       FOREIGN KEY (UserID, ActiveAddress)
       REFERENCES emails (UserID, EmailAddress),

   -- Validate the IsActive value makes sense    
   CONSTRAINT CK_emails_Validate_IsActive
   CHECK 
   (
       (IsActive = true AND EmailAddress = ActiveAddress)
       OR
       (IsActive = false AND EmailAddress <> ActiveAddress)
   )
);

-- Enforce maximum of one active address per user
CREATE UNIQUE INDEX UQ_emails_One_IsActive_True_PerUser
ON emails (UserID, IsActive)
WHERE IsActive = true;

[Test SQLFiddle](http://sqlfiddle.com/#!15/5ef3c/4)

在a_horse_with_no_name的幫助下，從我的本地 SQL Server 翻譯而來

正如評論中提到的ypercube，您甚至可以走得更遠：

• 刪除布爾列；和
• 創建UNIQUE INDEX ON emails (UserID) WHERE (EmailAddress = ActiveAddress)

效果是一樣的，但可以說更簡單、更整潔。

---

1問題在於現有約束僅確保存在被另一行稱為“活動”的行，而不是它實際上也處於活動狀態。我不太了解 Postgres，無法自己實現額外的約束（至少現在不是），但在 SQL Server 中，可以這樣做：

CREATE TABLE Emails 
(
   EmailID integer NOT NULL UNIQUE,
   UserID integer NOT NULL,
   EmailAddress varchar(254) NOT NULL,
   IsActive bit NOT NULL,

   -- New columns
   ActiveEmailID integer NOT NULL,
   ActiveIsActive AS CONVERT(bit, 'true') PERSISTED,

   -- Obvious PK
   CONSTRAINT PK_emails_UserID_EmailAddress
       PRIMARY KEY (UserID, EmailID),

   CONSTRAINT UQ_emails_UserID_EmailAddress_IsActive
       UNIQUE (UserID, EmailID, IsActive),

   -- Validate that the active address exists and is active
   CONSTRAINT FK_emails_ActiveAddressExists_And_IsActive
       FOREIGN KEY (UserID, ActiveEmailID, ActiveIsActive)
       REFERENCES emails (UserID, EmailID, IsActive),

   -- Validate the IsActive value makes sense    
   CONSTRAINT CK_emails_Validate_IsActive
   CHECK 
   (
       (IsActive = 'true' AND EmailID = ActiveEmailID)
       OR
       (IsActive = 'false' AND EmailID <> ActiveEmailID)
   )
);

-- Enforce maximum of one active address per user
CREATE UNIQUE INDEX UQ_emails_One_IsActive_PerUser
ON emails (UserID, IsActive)
WHERE IsActive = 'true';

通過使用代理而不是複製完整的電子郵件地址，這項工作在原件上有所改進。

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