PostgreSQL多约束冲突下实现未删除行service_id与subservice_id唯一校验

问题原因说明 #

你编写的CHECK约束语法错误是因为：CHECK约束只能对单行数据的列值规则做校验，无法实现跨行的唯一性校验，这类部分唯一性的需求需要用部分（过滤）唯一索引实现。 #

实现步骤 #

第一步：清理现有脏数据 #

建索引前需要先处理已存在的重复未删除数据，否则索引会创建失败。你可以根据业务需要保留每组重复数据中的某一行，其余标记为已删除，示例SQL如下：

WITH duplicate_rows AS (
    SELECT 
        id,
        -- 按业务规则排序，示例保留id最大的行，可自行调整排序逻辑，比如优先保留有service_uuid的行
        ROW_NUMBER() OVER (PARTITION BY service_id, subservice_id ORDER BY id DESC) AS rn
    FROM service
    WHERE is_deleted = false
)
UPDATE service
SET is_deleted = true
WHERE id IN (SELECT id FROM duplicate_rows WHERE rn > 1);

第二步：创建部分唯一索引 #

该索引仅对is_deleted = false的行校验service_id和subservice_id的组合唯一性，已删除的行不受限制：

CREATE UNIQUE INDEX idx_service_unique_active_pair 
ON service (service_id, subservice_id) 
WHERE is_deleted = false;

第三步：修改批量插入的冲突更新逻辑 #

将原基于service_uuid的冲突规则改为匹配上面的部分索引规则即可：

INSERT INTO service (service_id, service_uuid, subservice_id, is_deleted, ...其他字段)
VALUES (...你的插入值集合...)
ON CONFLICT (service_id, subservice_id) WHERE is_deleted = false
DO UPDATE SET
    -- 按业务需要设置需要更新的字段，EXCLUDED指代本次尝试插入的新行对应的值
    service_uuid = EXCLUDED.service_uuid,
    -- 其他字段更新逻辑，如更新时间等
    update_time = NOW();

兼容MySQL的备选方案 #

如果你使用的是MySQL（MySQL原生不支持部分唯一索引），可以用函数索引实现同等效果：

-- 仅支持MySQL 8.0及以上版本
CREATE UNIQUE INDEX idx_service_unique_active_pair 
ON service (service_id, subservice_id, (IF(is_deleted = false, 1, NULL)));

利用唯一索引不限制NULL值重复的特性，已删除的行对应第三个字段值为NULL，不会触发唯一性校验。

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