PostgreSQL条件约束：避免用户重复订阅同一服务的最优实现

针对你的需求，PostgreSQL的**部分唯一索引（Partial Unique Index）**是比自定义函数+CHECK约束更可靠、高效的解决方案，完美匹配你的约束规则。

问题分析 #

你的核心需求是：仅当subscription_status为'subscribed'时，user_id和subscription_id的组合必须唯一；而'pending'或'cancelled'状态的记录不受此限制，可以重复。

你之前用自定义函数+CHECK约束的方案存在以下隐患：

• 并发场景下会出现竞态条件：两个事务同时插入同用户同订阅的'subscribed'记录时，函数查询无法看到对方未提交的数据，导致重复插入成功
• 性能损耗：自定义函数需要查询表数据，插入/更新时的检查效率远低于数据库原生索引
• 维护成本高：函数逻辑需要随业务变化同步更新，且CHECK约束默认不会自动检查已存在的历史数据

实现代码 #

假设你的表名为subscriptions，执行以下SQL创建部分唯一索引：

CREATE UNIQUE INDEX idx_unique_active_subscription
ON subscriptions (user_id, subscription_id)
WHERE subscription_status = 'subscribed';

方案说明 #

1. 约束生效范围：该索引仅对subscription_status = 'subscribed'的行生效，确保同一user_id+subscription_id组合下最多存在一条活跃订阅记录
2. 其他状态不受限：'pending'或'cancelled'状态的记录不在索引的过滤条件内，因此可以任意插入多条
3. 符合你的示例场景：
   • 用户151的subscription_id=1只能有一条'subscribed'记录
   • 用户151的subscription_id=5可以同时存在'subscribed'和'cancelled'记录
   • subscription_id=3、4的'cancelled'记录可以有多条

方案优势 #

• 并发安全：数据库原生处理唯一性检查，避免竞态条件导致的约束违反
• 性能优异：唯一索引的检查逻辑是数据库优化后的原生操作，比自定义函数高效得多
• 维护简单：无需编写和维护自定义函数，直接依赖数据库原生特性
• 数据一致性：创建索引时会自动检查现有数据是否符合约束，若存在违反的记录会直接报错，确保历史数据的合法性

注意事项 #

如果你的表中已存在违反约束的历史数据（比如同一user_id+subscription_id下有多条'subscribed'记录），创建索引会失败。此时需要先清理重复数据，再执行索引创建语句。

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