PostgreSQL 唯一索引中存在值为空的字段导致索引失效


问题回顾

PostgreSQL 数据库,对角色表(role_info)中的4个字段,新建了一个唯一索引,表和字段说明如下:

biz_system_id、role_code、source_from三个字段不可为空;

rel_biz_system_id可为空

系统id(biz_system_id)
角色编码(role_code)
角色来源(source_from)
关联系统id(rel_biz_system_id)




创建索引语句

CREATE unique INDEX IDX_UNIQUE_ROLE ON public.role_info USING BTREE (biz_system_id,role_code,source_from,rel_biz_system_id);
COMMENT ON INDEX IDX_UNIQUE_ROLE IS '角色唯一索引';

预期结果

对于 rel_biz_system_id 字段都为空、且biz_system_id、role_code、source_from三个字段值相同的两条记录,不能重复插入到角色表。

实际结果

唯一索引未生效,角色表中插入了重复数据。

解决方案

创建索引语句修改为:

CREATE unique INDEX idx_unique_role_relsysid_notnull ON public.role_info USING BTREE (biz_system_id,role_code,source_from,rel_biz_system_id) WHERE rel_biz_system_id is not null;
COMMENT ON INDEX idx_unique_role_relsysid_notnull IS '角色唯一索引(关联系统id不为空)'; 

CREATE unique INDEX idx_unique_role_relsysid_null ON public.role_info USING BTREE (biz_system_id,role_code,source_from) WHERE rel_biz_system_id is null;
COMMENT ON INDEX idx_unique_role_relsysid_null IS '角色唯一索引(关联系统id为空)';

发表评论

0 评论
  • 最新评论
  • 按热度排序