PostgreSQL配置唯一约束实现每个部门仅1条活跃案件记录

普通的(department_id, active)联合唯一约束无法满足需求，因为同一个部门下如果存在多条active = FALSE的记录，两个字段值完全重复，会触发约束报错，不符合允许多条非活跃案件存在的要求。

正确的实现方式是创建带筛选条件的部分唯一索引，仅对活跃状态的记录做唯一性校验，非活跃记录不受约束限制：

-- PostgreSQL、MySQL 8.0+、SQL Server 均支持该语法
CREATE UNIQUE INDEX idx_cases_single_active_department
ON cases (department_id)
WHERE active = TRUE;

实现逻辑说明 #

• 该索引只会收录active = TRUE的案件记录
• 对索引内的记录强制department_id唯一，即同一个部门最多只能有1条记录进入索引，从数据库层面保证最多1条活跃案件
• 所有active = FALSE的非活跃记录不会进入索引，同一个部门下无论存储多少条非活跃案件，都不会触发约束冲突

低版本数据库兼容方案 #

如果使用的数据库版本不支持部分唯一索引，可以通过可空标记字段+普通唯一索引实现同等效果：

1. 将原active布尔字段替换为可空的标记字段，比如active_flag bigint
2. 案件为活跃状态时，将active_flag的值设为对应department_id；案件为非活跃状态时，将active_flag设为NULL
3. 给active_flag字段加唯一索引即可

CREATE UNIQUE INDEX idx_cases_single_active_department ON cases(active_flag);

这个方案利用了数据库唯一约束对多个NULL值不判定为重复的特性，效果和部分唯一索引一致，但需要额外维护字段状态，优先推荐使用部分唯一索引方案。

注意：不建议通过业务代码或者触发器实现该约束，这类实现方式在并发写入场景下容易出现竞态问题导致约束失效，数据库原生索引是在存储层做并发校验，安全性和性能都更优。

内容的提问来源于stack exchange，提问作者user1354934