Skip to main content
 首页 » 数据库

PostgreSQL组合唯一约束空值问题

2022年07月19日179三少

因为 PostgreSQL唯一约束并考虑空值的唯一性,我们虽然在列上定义了唯一约束,但仍然会存在重复数据。PostgreSQL唯一约束的规则是,唯一键的列值可以为NULL。

问题描述

在多个列上定义组合唯一键,那么当其中一个值为空而其他值不为空时约束不起作用,下面看详细过程。

  • 创建表
CREATE TABLE TestUniqueNull 
( 
    ID INTEGER  
    ,NoA INTEGER  
    ,NoB INTEGER  
    ,NoC INTEGER  
    ,CONSTRAINT pk_tbl_TestUniqueNull_ID PRIMARY KEY(ID) 
    ,CONSTRAINT uk_tbl_TestUniqueNull_NoA_NoB_NoC unique (NoA,NoB,NoC) 
); 
  • 插入示例数据
INSERT INTO TestUniqueNull VALUES (1,1,2,NULL); 
INSERT INTO TestUniqueNull VALUES (2,1,2,NULL); 
INSERT INTO TestUniqueNull VALUES (3,1,5,NULL); 
INSERT INTO TestUniqueNull VALUES (4,3,NULL,1); 
INSERT INTO TestUniqueNull VALUES (5,3,NULL,1); 
  • 查看数据
id noa nob noc
1 1 2
2 1 2
3 1 5
4 3 1
5 3 1

我们看到当列值为空时,存在重复记录,违背了定义唯一约束的需求。

使用唯一索引

针对上面的问题,我们使用唯一索引代替组合唯一约束:

CREATE TABLE TestUniqueNull 
( 
    ID INTEGER  
    ,NoA INTEGER  
    ,NoB INTEGER  
    ,NoC INTEGER  
    ,CONSTRAINT pk_tbl_TestUniqueNull_ID PRIMARY KEY(ID) 
); 
 
CREATE UNIQUE INDEX UIdx_NoA_NoB_NoC  
                    ON TestUniqueNull(coalesce(NoA,-1),coalesce(NoB,-1),coalesce(NoC,-1)); 

再次插入示例会报错:

SQL 错误 [23505]: 错误: 重复键违反唯一约束 “uidx_noa_nob_noc”
Detail: 键值"(COALESCE(noa, ‘-1’::integer), COALESCE(nob, ‘-1’::integer), COALESCE(noc, ‘-1’::integer))=(1, 2, -1)" 已经存在

总结

本文描述PostgreSQL 组合唯一约束空值问题,并给出使用唯一索引代替约束的解决方法。


本文参考链接:https://blog.csdn.net/neweastsun/article/details/119649930
阅读延展