PostgreSQL 多列唯一約束和 NULL 值

我有一個如下表：

create table my_table (
   id   int8 not null,
   id_A int8 not null,
   id_B int8 not null,
   id_C int8 null,
   constraint pk_my_table primary key (id),
   constraint u_constrainte unique (id_A, id_B, id_C)
);

我想(id_A, id_B, id_C)在任何情況下都與眾不同。所以下面的兩個插入一定會導致錯誤：

INSERT INTO my_table VALUES (1, 1, 2, NULL);
INSERT INTO my_table VALUES (2, 1, 2, NULL);

但它的行為不像預期的那樣，因為根據文件，兩個NULL值沒有相互比較，所以兩個插入都沒有錯誤地通過。

即使在這種情況下，我如何保證我的唯一id_C約束NULL？實際上，真正的問題是：我可以在“純 sql”中保證這種唯一性，還是必須在更高級別上實現它（在我的例子中是 java）？

您可以在純 SQL中做到這一點。除了您擁有的索引之外，還創建一個**部分唯一索引：**

CREATE UNIQUE INDEX ab_c_null_idx ON my_table (id_A, id_B) WHERE id_C IS NULL;

這樣你就可以(id_A, id_B, id_C)在你的表中輸入：

(1, 2, 1)
(1, 2, 2)
(1, 2, NULL)

但這些都沒有第二次。

或者使用兩個部分UNIQUE索引並且沒有完整索引（或約束）。最佳解決方案取決於您的需求細節。比較：

• 使用空列創建唯一約束

雖然這對於索引中的單個可空列來說是優雅和高效的UNIQUE，但對於不止一個列，它很快就會失控。討論這個 - 以及如何使用帶有部分索引的 UPSERT：

• 具有 NULL 值的 PostgreSQL UPSERT 問題

旁白

在 PostgreSQL 中不使用沒有雙引號的混合大小寫標識符。

您可以將serial列視為主鍵或 Postgres 10 或更高版本中的IDENTITY列。有關的：

• 自動遞增表列

所以：

CREATE TABLE my_table (
  my_table_id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY  -- for pg 10+
-- my_table_id bigserial PRIMARY KEY  -- for pg 9.6 or older
, id_a int8 NOT NULL
, id_b int8 NOT NULL
, id_c int8
, CONSTRAINT u_constraint UNIQUE (id_a, id_b, id_c)
);

如果您不希望在表的生命週期內（包括浪費和刪除的行）有超過 20 億行 (> 2147483647)，請考慮integer使用（4 字節）而不是bigint（8 字節）。

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