用 PostgreSQL 部分唯一索引解决软删除后的邮箱唯一冲突

问题场景

用户注销通常采用软删除：把 deleted_at 置为时间戳，而不是执行 DELETE FROM users。这样做数据还在，审计、申诉、恢复都有依据。

但 users.email 上的普通唯一约束会跟着出问题：注销后用户想用同一个邮箱重新注册，插入会被唯一约束直接拒绝，因为那条已软删除的记录仍然占用邮箱。

ALTER TABLE users ADD CONSTRAINT users_email_key UNIQUE (email);

INSERT INTO users (email) VALUES ('user@example.com');
UPDATE users SET deleted_at = now() WHERE email = 'user@example.com';
INSERT INTO users (email) VALUES ('user@example.com');
-- ERROR: duplicate key value violates unique constraint "users_email_key"

物理删除能绕开这个问题，但会丢掉审计与关联数据，多数业务不接受。

解决方向：部分唯一索引

PostgreSQL 支持部分索引（partial index）：只对满足谓词的行建立索引条目。把唯一索引的谓词限制成 deleted_at IS NULL，就只有活跃行参与唯一性校验，已软删除的行不再占用邮箱。

注意唯一约束（constraint）本身不支持部分谓词，这里只能用唯一索引。索引同样会阻止重复插入，效果等同约束。

可运行的最小示例

先建一张最简的表，并用部分唯一索引代替原来的唯一约束：

CREATE TABLE users (
  id         BIGSERIAL PRIMARY KEY,
  email      TEXT NOT NULL,
  deleted_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX users_email_active_uidx
  ON users (email)
  WHERE deleted_at IS NULL;

如果表上已有普通唯一约束，迁移时先删掉再建部分索引。在线环境可考虑 CREATE UNIQUE INDEX CONCURRENTLY（它不能在事务块中执行，中断后可能留下无效索引，需要确认后清理）：

ALTER TABLE users DROP CONSTRAINT IF EXISTS users_email_key;

CREATE UNIQUE INDEX CONCURRENTLY users_email_active_uidx
  ON users (email)
  WHERE deleted_at IS NULL;

验证行为：

INSERT INTO users (email) VALUES ('user@example.com');

-- 软删除第一条
UPDATE users SET deleted_at = now() WHERE email = 'user@example.com';

-- 同一邮箱可以再次注册
INSERT INTO users (email) VALUES ('user@example.com');

-- 再插一次，活跃记录重复，报 23505
INSERT INTO users (email) VALUES ('user@example.com');

在 psql 里执行以上语句即可复现：前两次插入成功，第三次报唯一冲突。查询也可以带上相同谓词，让规划器考虑使用这个索引（数据量很小时走顺序扫描属于正常现象）：

SELECT id FROM users WHERE email = 'user@example.com' AND deleted_at IS NULL;

配合 ON CONFLICT

如果业务里有“存在则更新”的插入，部分索引需要在 ON CONFLICT 中显式带上谓词，让 PostgreSQL 能把语句推断到该索引上：

INSERT INTO users (email)
VALUES ('user@example.com')
ON CONFLICT (email) WHERE deleted_at IS NULL
DO UPDATE SET created_at = now();

谓词写法保持与索引定义一致，是避免推断失败最省事的做法。

边界与限制

• 部分唯一索引不能作为外键的被引用目标，外键要求引用一个完整的唯一约束或主键。若其他表通过邮箱引用 users，需要另设代理键。
• 建索引时会对谓词覆盖的行做一次唯一性校验。如果历史数据里已经存在多条活跃邮箱重复，建索引会失败，必须先归并或改写数据。
• 恢复软删除记录时把 deleted_at 置回 NULL，可能与现有活跃记录冲突，应用层应捕获 SQLSTATE 23505 并给出业务提示，而不是返回 500。
• 表上同时存在多个部分唯一索引时，ON CONFLICT 的推断可能无法唯一确定目标，此时应改写语句避免推断，或明确使用其他冲突处理方式。
• email 为 NULL 的行不参与唯一性比较，这是 PostgreSQL 唯一索引的常规语义；如果业务要求把空邮箱也视为重复，需要额外处理。
• 索引只覆盖活跃行，体积和写入维护成本通常小于全量唯一索引，但软删除行越多，索引与表数据的不一致程度越高，定期归档仍是必要的。

小结

软删除与邮箱唯一性并不矛盾：用 CREATE UNIQUE INDEX ... WHERE deleted_at IS NULL 把“唯一”限定在活跃数据上，既保留了历史记录，又允许邮箱被重新使用。落地时重点检查三件事：历史活跃数据是否已有重复、是否存在以邮箱为外键的引用、以及应用层是否正确处理恢复时的冲突。