PostgreSQL多列唯一约束和NULL值

发布时间：2020-12-13 16:20:34 所属栏目：百科来源：网络整理

导读：我有一个如下表： 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_C)在任何情况下都是不同的

我有一个如下表：

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_C)在任何情况下都是不同的.因此,以下两个插入必须导致错误：

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

但它没有按预期运行,因为根据文档,两个NULL值不会相互比较,因此两个插入都没有错误地传递.

即使在这种情况下id_C可以为NULL,我如何保证我的唯一约束？
实际上,真正的问题是：我可以保证“纯sql”中的这种独特性,还是我必须在更高的层次上实现它(在我的情况下是java)？

您可以在纯SQL中执行此操作.除了你拥有的那个之外,创建一个 partial unique index：

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

这样您就可以在表格中输入(a,b,c)：

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

但这些都不是第二次.

或者使用两个部分UNIQUE索引,没有完整的索引(或约束).最佳解决方案取决于您的要求的详细信息.相比：

> Create unique constraint with null columns

旁白

>在PostgreSQL中没有用于mixed case identifiers without double quotes.
>您可以将serial column视为主键或Postgres 10或更高版本中的IDENTITY column.

> Auto increment table column

所以：

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),请考虑整数(4个字节)而不是bigint(8个字节).

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!