让唯一约束与软删除共存

用部分唯一索引替换普通唯一约束，活跃行保持唯一，已删除行不再永久占用邮箱等业务标识。

概述

软删除保留整行数据，以维持历史记录与外键完整性，但普通的 UNIQUE 约束分不清活跃行与墓碑行，邮箱等标识会被永久占用。团队常见的两种补救都不理想：复用已删除的行会覆盖历史；彻底去掉唯一约束又会允许两个活跃账号使用同一邮箱。真正的业务规则是“活跃行之间唯一”，而 PostgreSQL 的部分索引正好能表达这一点。

步骤

先确认现有约束：

SELECT conname, pg_get_constraintdef(oid) FROM pg_constraint
WHERE conrelid = 'users'::regclass AND contype = 'u';

排查活跃行中已存在的重复值，有则先合并或改名，否则建索引会失败：

SELECT lower(email), count(*) FROM users
WHERE deleted_at IS NULL GROUP BY 1 HAVING count(*) > 1;

删除旧约束并创建部分唯一索引：

ALTER TABLE users DROP CONSTRAINT users_email_key;
CREATE UNIQUE INDEX users_email_active_uidx
  ON users (lower(email))
  WHERE deleted_at IS NULL;

同步修改所有写入路径，ON CONFLICT 必须重复索引谓词才能推断到该索引：

INSERT INTO users (email) VALUES ('[email protected]')
ON CONFLICT (lower(email)) WHERE deleted_at IS NULL DO NOTHING;

验证

先插入一条 deleted_at 非空的同邮箱记录，再插入活跃记录应成功，第二次插入活跃记录应报 SQLSTATE 23505 并指向 users_email_active_uidx。随后用 EXPLAIN (ANALYZE, BUFFERS) 检查带 deleted_at IS NULL 的查询走索引扫描，不带该谓词的查询会退化为顺序扫描。

注意事项

查询必须带上 deleted_at IS NULL 才能使用部分索引，管理后台与报表 SQL 往往遗漏这个条件。
该索引允许多条已删除行使用同一邮箱，这通常符合预期，但统计类查询需要知晓这一点。
大小写不敏感只在所有写入方都使用 lower(email) 时成立，需在应用层统一。
恢复已删除行时要显式处理冲突：若已有活跃行占用该值，应合并或返回业务冲突，而不是直接清空 deleted_at。
ORM 往往无法声明部分索引，迁移文件需手写 SQL，并检查下一次自动生成迁移没有把它改回完整唯一约束。