PostgreSQL更新多行时唯一键约束冲突的便捷解决方案咨询

针对你遇到的交换两条记录order_index触发唯一约束冲突的问题，有两种更便捷的解决方案，不用先把值改成临时的4：

1. 单条UPDATE语句批量更新 #

大部分关系型数据库（比如MySQL、PostgreSQL）支持在单条UPDATE里通过CASE语句或自连接完成交换，数据库会一次性计算所有新值再批量写入，不会中间出现重复值，自然不会触发唯一约束。

用CASE语句的写法：

UPDATE your_table
SET order_index = CASE id
    WHEN 1 THEN 2
    WHEN 2 THEN 1
    ELSE order_index
END
WHERE id IN (1, 2);

或者用自连接的写法（适配MySQL）：

UPDATE your_table t1
JOIN your_table t2 
  ON t1.id IN (1, 2) 
  AND t2.id IN (1, 2) 
  AND t1.id != t2.id
SET t1.order_index = t2.order_index
WHERE t1.id IN (1, 2);

2. 延迟约束检查（部分数据库支持） #

像PostgreSQL、Oracle这类支持延迟约束的数据库，可以开启事务并延迟约束检查，这样就能分开执行两条UPDATE，直到事务提交时才验证约束：

BEGIN;
-- 延迟所有约束检查到事务提交阶段
SET CONSTRAINTS ALL DEFERRED;
UPDATE your_table SET order_index = 2 WHERE id = 1;
UPDATE your_table SET order_index = 1 WHERE id = 2;
COMMIT;

这两种方法都比先改到临时值再调整要高效直接，无需额外查询最大值或临时值。

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