如何更新带联合唯一约束的关系表字段且不删约束不重建表？

关系表id_rel包含id、other_id两个字段，两字段设有联合唯一约束，表内初始数据示例如下：

id | other_id
-----------
1  | 123
-----------
2  | 456
-----------
3  | 123

需求为将表中所有other_id=123的记录更新为other_id=456，直接执行如下UPDATE语句会触发约束报错：

UPDATE id_rel
SET other_id = 456
WHERE other_id = 123;

报错信息：

ERROR: duplicate key value violates unique constraint "id_rel" Detail: Key (id, other_id)=(1, 456) already exists.

要求不删除唯一约束、不重建表完成更新操作。

触发冲突的核心是更新操作会产生重复的(id, other_id)组合：部分id同时绑定了123和456两个other_id值，直接将123改为456会导致同一个id对应两条other_id=456的记录，违反联合唯一约束规则。
如果确认更新后的最终结果不存在重复组合，仅更新过程中数据库逐行校验约束产生临时冲突（如交换两个other_id取值的场景），属于校验机制导致的问题，无需删改约束即可处理。

全程不需要删除唯一约束、不需要重建表，根据实际业务场景选择对应方案即可。

场景1：需要合并关联关系，同id仅保留一条456关联 #

业务要求同一个id只能保留一条绑定456的记录，原来绑定123的重复记录直接删除，按以下步骤操作：

• 第一步：删除同id下已经绑定过456的重复123记录
  • PostgreSQL、MySQL 8.0及以上版本直接执行：

  DELETE FROM id_rel
  WHERE other_id = 123
    AND id IN (SELECT id FROM id_rel WHERE other_id = 456);
  

  • 低版本MySQL不支持子查询直接查询同表做删除，需要嵌套一层临时表绕过限制：

  DELETE FROM id_rel
  WHERE other_id = 123
    AND id IN (
      SELECT t.id FROM (
        SELECT id FROM id_rel WHERE other_id = 456
      ) t
    );
  

• 第二步：执行常规更新语句，把剩余绑定123的记录改为456，此时不会再有重复冲突：

UPDATE id_rel
SET other_id = 456
WHERE other_id = 123;

如果希望一步完成操作，可以使用可写CTE（支持PostgreSQL、MySQL 8.0+）：

WITH delete_duplicate AS (
    DELETE FROM id_rel
    WHERE other_id = 123
      AND id IN (SELECT id FROM id_rel WHERE other_id = 456)
)
UPDATE id_rel
SET other_id = 456
WHERE other_id = 123;

场景2：最终结果无重复，仅更新过程临时冲突 #

确认所有id更新后不会出现(id, other_id)重复，只是数据库逐行校验约束导致报错，可以选择以下两种方法：

• 方法1：临时值过渡
  选择一个业务中绝对不会使用的other_id值作为临时中转值（比如-1、999999这类无效值），分两步更新避开冲突：

  -- 第一步：先把所有123改为临时值，不会和现有456冲突
  UPDATE id_rel SET other_id = -1 WHERE other_id = 123;
  -- 第二步：再把临时值改为目标值456
  UPDATE id_rel SET other_id = 456 WHERE other_id = -1;
  

• 方法2：延迟约束校验（适合PostgreSQL、Oracle等支持延迟约束的数据库）
  调整约束校验时机为事务提交后再校验，避开逐行更新的临时冲突，不会删除原有约束：

  1. 先把约束修改为可延迟模式（仅需执行一次，后续不需要重复操作，注意替换成你实际的约束名）：

  ALTER TABLE id_rel ALTER CONSTRAINT id_rel_unique DEFERRABLE;
  

  2. 开启事务，设置约束延迟校验后执行更新：

  BEGIN;
  SET CONSTRAINTS ALL DEFERRED;
  -- 执行原更新语句
  UPDATE id_rel SET other_id = 456 WHERE other_id = 123;
  -- 提交时才会校验最终结果的唯一性，无重复就会执行成功
  COMMIT;
  

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