PostgreSQL能否创建仅在事务提交时校验的唯一约束/索引？

嘿，这个场景我太熟悉了——很多ORM或框架在处理排序重排时，都会先更新中间的排序值，导致事务还没提交就触发唯一约束冲突。PostgreSQL确实有办法解决这个问题，核心思路是让约束的校验延迟到事务提交时执行，下面给你几个靠谱的方案：

1. 使用可延迟的唯一约束（最推荐） #

PostgreSQL支持可延迟约束（Deferrable Constraints），这正是为这种事务内中间状态可能违反约束的场景设计的。你可以把唯一约束设置为延迟校验，这样只有在事务提交时才会检查是否违反约束，而不是每执行一条UPDATE语句就立刻校验。

创建可延迟唯一约束的两种方式： #

方式一：创建表时直接定义

CREATE TABLE your_table (
    id SERIAL PRIMARY KEY,
    record_id INT NOT NULL,
    ordering INT NOT NULL,
    CONSTRAINT uq_record_order UNIQUE (record_id, ordering) DEFERRABLE INITIALLY DEFERRED
);

这里的DEFERRABLE INITIALLY DEFERRED表示这个约束默认就是延迟校验的，每次事务都会在提交时才检查。

方式二：给现有表添加可延迟约束

ALTER TABLE your_table
ADD CONSTRAINT uq_record_order UNIQUE (record_id, ordering) DEFERRABLE INITIALLY DEFERRED;

如果需要灵活控制校验时机 #

如果你不想默认延迟，也可以设置为INITIALLY IMMEDIATE（默认行为），然后在需要重排的事务里手动切换为延迟：

BEGIN;
-- 临时将约束设置为延迟校验
SET CONSTRAINTS uq_record_order DEFERRED;

-- 执行你的排序重排操作，比如先把某条记录的ordering改成临时值，再修改其他记录，最后调整回来
UPDATE your_table SET ordering = 999 WHERE id = 1;
UPDATE your_table SET ordering = 1 WHERE id = 2;
UPDATE your_table SET ordering = 2 WHERE id = 1;

COMMIT; -- 这时候才会校验约束是否满足

2. 排他约束（Exclusion Constraints）作为备选 #

如果你的场景还有更复杂的需求（比如范围冲突），可以考虑用排他约束，但对于单纯的唯一值校验来说，可延迟唯一约束更轻量、更合适。不过还是提一下用法：

ALTER TABLE your_table
ADD CONSTRAINT ex_record_order EXCLUDE USING btree (record_id WITH =, ordering WITH =) DEFERRABLE INITIALLY DEFERRED;

它的效果和可延迟唯一约束类似，但功能更强大，不过性能上可能略逊于普通唯一约束。

不推荐的方案：临时禁用约束 #

虽然可以用ALTER TABLE your_table DISABLE TRIGGER ALL;临时禁用约束，但这种方法风险很高——如果事务中途失败，可能会留下脏数据，而且需要超级用户权限，所以除非万不得已，不要用这个方法。

总结一下，可延迟的唯一约束是解决你这个问题的最佳实践，完美适配框架重排时的中间状态，同时保证最终数据的唯一性。

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