如何強制執行“每組一個”約束？

我有一個x這樣定義的表：

CREATE TABLE x (
   xid INTEGER NOT NULL PRIMARY KEY,
   yid INTEGER NOT NULL REFERENCES y(yid),
   is_principal BOOLEAN NOT NULL
);

這個定義遺漏了一個x必須滿足的約束。在英語中，這個約束可以這樣描述：

欄位中可能有一行或多行具有給定值yid，但其中必須始終恰好有一行的is_principal欄位為TRUE1。

我正在尋找一種方法來強制執行此約束。

（如果重要的話，我對適用於 SQLite3 和 PostgreSQL 的解決方案特別感興趣。）

編輯：為了清楚起見，上面的描述並不排除 table 中的行的存在，y其值在 table 中yid根本沒有提到x。對於這樣的價值，yid根本沒有價值xid，本金或其他。僅對於yid 出現在表 x中的那些值，表中必須只有一行x具有is_principal = TRUE.

---

1表達相同約束的另一種方式是說以下兩個查詢應始終產生相同的輸出：

SELECT DISTINCT yid FROM x                    ORDER BY yid;
SELECT          yid FROM x WHERE is_principal ORDER BY yid;

問題與此類似：如何與特權孩子建立一對多的關係？

約束的“每組最多一個”部分可以用部分索引來解決：

CREATE UNIQUE INDEX is_FavoriteChild
 ON x (yid)
 WHERE is_principal  ;

解決問題的另一種方法是刪除is_principal列並添加第三個表。這也不能解決“確切的問題”：

CREATE TABLE x (
   xid INTEGER NOT NULL PRIMARY KEY,
   yid INTEGER NOT NULL REFERENCES y (yid),
   UNIQUE (yid, xid)
);

CREATE TABLE x_principal (
   xid INTEGER NOT NULL,
   yid INTEGER NOT NULL PRIMARY KEY,
   FOREIGN KEY (yid, xid) REFERENCES x (yid, xid) 
);

---

如果您想單獨使用 DDL 強制執行“完全一個”限制，可以在 Postgres 中使用可延遲約束來完成（我認為這不是 SQLite 中的選項）。

有關更多詳細資訊和選項，您可以在@Erwin 的 SO 問題中看到出色的答案：SQLAlchemy 中的複雜外鍵約束。

（編輯附加詳細資訊的答案，即並非所有值y.yid都必須出現在 table 中x。添加了一個表）：

--- This table will hold all values of yid that appear in table x
CREATE TABLE y_x (
   yid INTEGER NOT NULL PRIMARY KEY REFERENCES y (yid),
   --- **no other columns**
   principal_xid INTEGER NOT NULL
);

CREATE TABLE x (
   xid INTEGER NOT NULL PRIMARY KEY,
   yid INTEGER NOT NULL REFERENCES y_x (yid)
                        DEFERRABLE INITIALLY DEFERRED,
   UNIQUE (yid, xid)
);

ALTER TABLE y_x
ADD CONSTRAINT y_principal_x_fk
  FOREIGN KEY (yid, principal_xid)
  REFERENCES x (yid, xid)
  DEFERRABLE INITIALLY DEFERRED; 

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