PostgreSQL中带唯一约束的行更新报错，如何解决？

第一步：确认当前唯一约束的实际定义 #

你遇到的报错说明表上的唯一约束是(prop_1, prop_3)组合，而非你预期的三列全组合。先查询约束的具体列来验证：

PostgreSQL： #

SELECT conname, array_agg(attname) AS constraint_columns
FROM pg_constraint
JOIN pg_attribute ON conrelid = attrelid AND attnum = ANY(conkey)
WHERE conrelid = 'your_table_name'::regclass AND contype = 'u' AND conname = 'constraint_name'
GROUP BY conname;

MySQL： #

SHOW CREATE TABLE your_table_name;

查看输出中的UNIQUE KEY部分，确认约束包含的列。

第二步：排查冲突行的真实情况 #

你执行SELECT * FROM table WHERE prop_3 = 'cccc'返回0行，但报错提示(4444, cccc)存在，可能的原因：

• 存在prop_1=4444且prop_3='cccc'但prop_2不同的行，换用以下查询确认：

  SELECT * FROM your_table_name WHERE prop_1 = 4444 AND prop_3 = 'cccc';
  

• 数据库区分大小写（比如PostgreSQL默认区分），实际行的prop_3是'CCCC'或其他大小写变体，尝试大小写不敏感的查询：

  -- PostgreSQL
  SELECT * FROM your_table_name WHERE prop_1 = 4444 AND LOWER(prop_3) = 'cccc';
  
  -- MySQL（若表校对规则区分大小写）
  SELECT * FROM your_table_name WHERE prop_1 = 4444 AND prop_3 COLLATE utf8mb4_bin = 'cccc';
  

• 存在未提交的事务持有该组合的行，可尝试在新会话中查询，或重启数据库（测试环境）。

第三步：处理冲突并执行更新 #

如果查到冲突行，可根据需求处理：

• 删除冲突行：

  DELETE FROM your_table_name WHERE prop_1 = 4444 AND prop_3 = 'cccc';
  

• 修改冲突行的prop_3值：

  UPDATE your_table_name SET prop_3 = 'cccc_old' WHERE prop_1 = 4444 AND prop_3 = 'cccc';
  

处理完成后重新执行你的更新语句：

UPDATE your_table_name
SET prop_3 = 'cccc'
WHERE prop_1 = 4444;

第四步：重建三列组合的唯一约束 #

1. 删除现有约束 #

-- PostgreSQL
ALTER TABLE your_table_name DROP CONSTRAINT constraint_name;

-- MySQL
ALTER TABLE your_table_name DROP INDEX constraint_name;

2. 检查当前表中是否存在三列完全重复的行 #

创建新约束前需确保无重复，否则会创建失败：

SELECT prop_1, prop_2, prop_3, COUNT(*) AS duplicate_count
FROM your_table_name
GROUP BY prop_1, prop_2, prop_3
HAVING COUNT(*) > 1;

若有重复行，先删除或修改重复项。

3. 创建三列组合的唯一约束 #

ALTER TABLE your_table_name
ADD CONSTRAINT unique_full_row UNIQUE (prop_1, prop_2, prop_3);

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