PostgreSQL中如何实现特定枚举值的条件唯一约束？

问题描述 #

我需要为已有的PostgreSQL表添加特殊约束，表结构如下：

-- 先创建枚举类型
CREATE TYPE POST_TYPE AS ENUM ('question', 'option', 'answer');

-- 再创建QNA表
CREATE TABLE QNA (
    -- 省略其他字段
    post_id int,
    type POST_TYPE,
    description VARCHAR(100),
    -- 省略其他字段
);

其中post_id是外键，我的约束需求是：

• 每个post_id只能对应1条type为'question'的记录，也就是当type = 'question'时，post_id必须唯一；
• 如果type是'option'或'answer'，同一post_id下可以插入任意多条同类型记录，不需要唯一限制。

之前试过给(post_id, type)加联合唯一约束，但这样会导致同一post_id下连多条'option'记录都插不了，不符合需求，而且我不想修改现有表结构。

解决方案 #

完全可以实现，不需要改表结构，用PostgreSQL的部分唯一索引就能搞定，执行下面的SQL即可：

CREATE UNIQUE INDEX idx_qna_postid_question ON QNA (post_id)
WHERE type = 'question';

效果验证 #

针对post_id = 123的情况：

• 第一次插入(..., 123, 'question', '', ...) → 成功执行；
• 第二次插入同样的(..., 123, 'question', '', ...) → 触发唯一约束冲突，执行失败；
• 多次插入(..., 123, 'option', '', ...) → 每次都能成功执行，不受限制。

原理说明 #

部分唯一索引只对满足WHERE条件的记录生效：

• 当插入type = 'question'的记录时，索引会检查该post_id是否已经存在同类型记录，重复插入就会报错；
• 对于type为'option'或'answer'的记录，因为不满足索引的过滤条件，不会被这个唯一约束限制，所以可以随意插入。

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