跨實體的可選屬性強制唯一性

我有一個具有多個屬性的實體，這些屬性本身就是實體。

沒有兩個實體可能具有相同的屬性，因此它們之間需要有唯一性約束。但同時，並非所有屬性都是必需的。

下面的 DML 說明了 where attr_aandattr_b不是可選的（不允許 NULL），而attr_candattr_d是可選的（允許 NULL）：

CREATE TABLE attr_a (
 attr_a_id INTEGER(10) UNSIGNED PRIMARY KEY AUTO_INCREMENT
) ENGINE InnoDB;
CREATE TABLE attr_b (
 attr_b_id INTEGER(10) UNSIGNED PRIMARY KEY AUTO_INCREMENT
) ENGINE InnoDB;
CREATE TABLE attr_c (
 attr_c_id INTEGER(10) UNSIGNED PRIMARY KEY AUTO_INCREMENT
) ENGINE InnoDB;
CREATE TABLE attr_d (
 attr_d_id INTEGER(10) UNSIGNED PRIMARY KEY AUTO_INCREMENT
) ENGINE InnoDB;

CREATE TABLE entity (
 entity_id INT(10) UNSIGNED PRIMARY KEY AUTO_INCREMENT NOT NULL,
 attr_a_id INT(10) UNSIGNED NOT NULL,
 attr_b_id INT(10) UNSIGNED NOT NULL,
 attr_c_id INT(10) UNSIGNED NULL,
 attr_d_id INT(10) UNSIGNED NULL,
 UNIQUE KEY uq_attr (attr_a_id, attr_b_id, attr_c_id, attr_d_id),
 KEY `fk_attr_a_id` (attr_a_id),
 KEY `fk_attr_b_id` (attr_b_id),
 KEY `fk_attr_c_id` (attr_c_id),
 KEY `fk_attr_d_id` (attr_d_id),
 CONSTRAINT `fk_attr_a_id` FOREIGN KEY (attr_a_id) REFERENCES `attr_a` (attr_a_id),
 CONSTRAINT `fk_attr_b_id` FOREIGN KEY (attr_b_id) REFERENCES `attr_b` (attr_b_id),
 CONSTRAINT `fk_attr_c_id` FOREIGN KEY (attr_c_id) REFERENCES `attr_c` (attr_c_id),
 CONSTRAINT `fk_attr_d_id` FOREIGN KEY (attr_d_id) REFERENCES `attr_d` (attr_d_id)
) ENGINE InnoDB;

entity所以意圖是不允許表中的這兩行：

1,10,20,30,NULL
2,10,20,30,NULL

應該允許以下行對，就好像空值實際上是值一樣（我知道 BDB 引擎會允許這樣做，但我將使用 InnoDB。）

1,10,20,30,NULL
2,10,20,NULL,NULL

1,10,20,30,40
2,10,20,NULL,NULL

1,10,20,30,NULL
2,10,20,NULL,40

上面描述的表顯然是有缺陷的，因為 NULL 是如何使用唯一性約束來處理的——它將允許這兩行存在。

在entity,attr_c和的情況下attr_d是可選的而不是未知的。所以我認為允許 NULL 在技術上是不正確的，因為已知該值是未定義的，而不是未知的。

我認為唯一實用的解決方案是在每個屬性表中為“未定義”保留一個項目（可能 ID 為 0。）

我知道這個解決方案會起作用，但我想避免採用已知的反模式的錯誤。

沒有簡單的方法（SQL Server 除外，見下文）來強制執行這些約束。我說“這些”而不是“這個”，因為它們確實不止一個。

您希望(a,b,c,d)在所有屬性不為空時強制執行唯一性。並且(a,b,c)何時d為空的唯一性。並且(a,b,d)何時c為空的唯一性。並且(a,b)當兩者c都d為空時的唯一性。

您說NULL值 on cord表示該值已知為 undefined，但您同時允許(10, 20, 30, 40)and (10, 20, NULL, NULL)。在這種情況下，值是否已定義或已知未定義？

無論如何，要實際執行這些規則，有多種選擇：

---

如果要將數據保存在單個表中並在具有過濾/部分索引（PostgreSQL、SQL Server）的 DBMS 中實現，則可以創建 4 個UNIQUE部分索引：

UNIQUE (a, b, c, d) WHERE (c IS NOT NULL AND d IS NOT NULL)
UNIQUE (a, b, c)    WHERE (c IS NOT NULL AND d IS NULL)
UNIQUE (a, b, d)    WHERE (c IS NULL     AND d IS NOT NULL)
UNIQUE (a, b)       WHERE (c IS NULL     AND d IS NULL)

其他一些 DBMS（如 Oracle 和 DB2）沒有部分索引，但可以使用不同的技術來模擬它們（參見 Use the Index Luke! 網站：DB2，“Emulating partial index is possible）

---

特別是在 SQL Server 中 - 並且僅在此 DBMS 中，因為它處理與約束NULL有關的值與UNIQUE其他所有約束不同，而不是根據標準 - 我們並不真正需要所有 4 個約束，我們實際上可以通過你已經擁有的，一個簡單的UNIQUE約束(a,b,c,d)。

它將完全按照您的規則執行。這不符合 SQL 標準，並且行為在未來版本中發生變化的可能性很小（我不太可能添加，因為 DBMS 非常努力地實現向後兼容性）。

---

另一個（類似於部分唯一索引）選項（感謝@Erwin，請參閱他的答案）是使用功能索引。這可以在具有此類索引的 PostgreSQL、Oracle 和 DB2 中使用。

一種變體是使用計算列（用於屬性c和d）並基於四列添加唯一索引(a, b, coalesced_c, coalesced_d)。此功能在 SQL Server 中可用，在 MariaDB 5.3+（這是 MySQL 變體）和最新的 MySQL 版本 (5.7) 中也可用。

---

在較舊的 MySQL 版本（看起來像您使用的 DBMS）和其他沒有部分或功能索引的版本（如 SQLite）中，如果您只想保留一個表，我認為唯一的選擇是做您考慮的事情：

使用一個值（例如0或-1不能出現在您的數據中的值）代替*“known to be undefined”*。

這當然適用於任何 DBMS。

---

最後，您還可以在任何 DBMS 中使用（假設它支持常用FOREIGN KEY和UNIQUE約束）是將entity表正確規範化為四個，每種情況一個：

CREATE TABLE entity_ab (
 attr_a_id INT NOT NULL,
 attr_b_id INT NOT NULL,
 CONSTRAINT uq_attr_ab UNIQUE (attr_a_id, attr_b_id),
 CONSTRAINT fk_ab_attr_a_id FOREIGN KEY (attr_a_id) REFERENCES attr_a (attr_a_id),
 CONSTRAINT fk_ab_attr_b_id FOREIGN KEY (attr_b_id) REFERENCES attr_b (attr_b_id)
) ;

CREATE TABLE entity_abc (
 attr_a_id INT NOT NULL,
 attr_b_id INT NOT NULL,
 attr_c_id INT NOT NULL,
 CONSTRAINT uq_attr_abc UNIQUE (attr_a_id, attr_b_id, attr_c_id),
 CONSTRAINT fk_abc_attr_a_id FOREIGN KEY (attr_a_id) REFERENCES attr_a (attr_a_id),
 CONSTRAINT fk_abc_attr_b_id FOREIGN KEY (attr_b_id) REFERENCES attr_b (attr_b_id),
 CONSTRAINT fk_abc_attr_c_id FOREIGN KEY (attr_c_id) REFERENCES attr_c (attr_c_id)
) ;

CREATE TABLE entity_abd (
 attr_a_id INT NOT NULL,
 attr_b_id INT NOT NULL,
 attr_d_id INT NOT NULL,
 CONSTRAINT uq_attr_abd UNIQUE (attr_a_id, attr_b_id, attr_d_id),
 CONSTRAINT fk_abd_attr_a_id FOREIGN KEY (attr_a_id) REFERENCES attr_a (attr_a_id),
 CONSTRAINT fk_abd_attr_b_id FOREIGN KEY (attr_b_id) REFERENCES attr_b (attr_b_id),
 CONSTRAINT fk_abd_attr_d_id FOREIGN KEY (attr_d_id) REFERENCES attr_d (attr_d_id)
) ;

CREATE TABLE entity_abcd (
 attr_a_id INT NOT NULL,
 attr_b_id INT NOT NULL,
 attr_c_id INT NOT NULL,
 attr_d_id INT NOT NULL,
 CONSTRAINT uq_attr_abcd UNIQUE (attr_a_id, attr_b_id, attr_c_id, attr_d_id),
 CONSTRAINT fk_abcd_attr_a_id FOREIGN KEY (attr_a_id) REFERENCES attr_a (attr_a_id),
 CONSTRAINT fk_abcd_attr_b_id FOREIGN KEY (attr_b_id) REFERENCES attr_b (attr_b_id),
 CONSTRAINT fk_abcd_attr_c_id FOREIGN KEY (attr_c_id) REFERENCES attr_c (attr_c_id),
 CONSTRAINT fk_abcd_attr_d_id FOREIGN KEY (attr_d_id) REFERENCES attr_d (attr_d_id)
) ;

那麼entity表格將變為VIEW：

CREATE VIEW entity AS
       SELECT attr_a_id, attr_b_id, attr_c_id, attr_d_id
       FROM entity_abcd
   UNION ALL 
       SELECT attr_a_id, attr_b_id, attr_c_id, NULL
       FROM entity_abc
   UNION ALL 
       SELECT attr_a_id, attr_b_id, NULL, attr_d_id
       FROM entity_abd
   UNION ALL 
       SELECT attr_a_id, attr_b_id, NULL, NULL
       FROM entity_ab ;

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