主页 / dba / 问题 / 9759
Accepted
Manuel Leduc
Asked: 2011-12-28 01:10:21 +0800 CST

PostgreSQL 多列唯一约束和 NULL 值

  • 772

我有一个如下表：

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）？

postgresql constraint

3 个回答

  1. Best Answer

    Postgres 15

    这开箱即用NULLS NOT DISTINCT：

    ALTER TABLE my_table
  DROP CONSTRAINT IF EXISTS u_constrainte
, ADD CONSTRAINT u_constrainte UNIQUE NULLS NOT DISTINCT (id_A, id_B, id_C);

    看：

    Postgres 14 或以上（原始答案）

    您可以在纯 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：

    旁白

    在 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 字节）。

    • 155

  2. 我遇到了同样的问题，我找到了另一种将唯一 NULL 放入表中的方法。

    CREATE UNIQUE INDEX index_name ON table_name( COALESCE( foreign_key_field, -1) )

    就我而言，该字段foreign_key_field是一个正整数，永远不会是-1。

    因此，要回答 Manual Leduc，另一种解决方案可能是

    CREATE UNIQUE INDEX  u_constrainte (COALESCE(id_a, -1), COALESCE(id_b,-1),COALESCE(id_c, -1) )

    我假设 ids 不会是-1。

    创建部分索引有什么好处？
    如果您没有 NOT NULL 子句，id_a和id_b只能id_c同时为 NULL 一次。
    使用部分索引，这 3 个字段可能多次为 NULL。

    • 18

  3. Null 可能意味着该行的值目前未知，但在未来已知时将被添加（例如FinishDaterunning Project）或不能为该行应用任何值（例如EscapeVelocityblack hole Star）。

    在我看来，通常最好通过消除所有 Null 来规范化表。

    在您的情况下，您希望NULLs在您的列中允许，但您只想NULL允许一个。为什么？这两张表是什么关系？

    也许您可以简单地将列更改为NOT NULL并存储一个已知永远不会出现NULL的特殊值（如），而不是 。-1这将解决唯一性约束问题（但可能有其他可能不需要的副作用。例如，使用-1to 表示“未知/不适用”将扭曲列上的任何总和或平均计算。或者所有此类计算都必须采取考虑特殊值并忽略它。）

    • 9

相关问题