数据模型设计：如何实现跨组唯一组内可重复的唯一性约束

一、这类约束的定义方式 #

你的需求本质不是Account表的字段全局唯一，而是email、手机号这类联系方式的所有权，全局只能绑定到一个User，同一User组内的Account重复使用联系方式不受限制，不要直接在Account表给email/phone_number加普通唯一索引，两种可落地的实现：

• 独立所有权表方案（优先选，无并发一致性问题）
  单独建一张user_contact_binding表存联系方式的归属关系，核心字段：
  • id：主键
  • contact_type：枚举值，标记是email还是phone_number
  • contact_value：存储具体的邮箱、手机号字符串
  • user_id：关联绑定的所属用户ID
    直接给(contact_type, contact_value)建联合唯一索引，从数据库层强制同一个联系方式只能归属一个用户。
    原Account表保留user_id、email、phone_number字段即可，不需要给这两个联系方式字段加任何唯一约束，同一用户下多个Account存相同邮箱/手机号完全不受限制。
• 函数/部分唯一索引方案（适合不想新增表的场景，兼容性有限）
  如果你用的是PostgreSQL、MySQL 8.0+这类支持高级索引特性的数据库，可以在Account表上建特殊的唯一索引，只对每个用户下的第一条同联系方式记录做唯一校验，示例PostgreSQL语法：

  CREATE UNIQUE INDEX idx_unique_email_owner ON account (email, (
    CASE WHEN id = (SELECT min(id) FROM account a2 WHERE a2.user_id = account.user_id AND a2.email = account.email)
    THEN account.user_id
    ELSE NULL
    END
  ));
  

  这个方案维护成本高，并发写入时容易触发死锁，不建议核心业务、高并发场景用。

二、约束运维与增删记录的处理逻辑 #

用上面推荐的独立所有权表方案，逻辑最清晰，不会出现一致性问题：

新增Account记录流程 #

所有操作包裹在同一个数据库事务中执行：

1. 用待写入的联系方式类型+具体值，查询user_contact_binding表
2. 如果没有查到对应绑定记录：先在user_contact_binding插入当前用户与该联系方式的绑定关系，再插入新的Account记录，提交事务
3. 如果查到绑定记录，且绑定的user_id和当前操作用户ID一致：直接插入Account记录即可，不需要修改绑定表
4. 如果查到绑定记录，且绑定的user_id和当前操作用户ID不一致：直接返回联系方式已被占用的业务错误，终止事务

关键注意点：不要只靠业务代码做前置校验，必须靠数据库的唯一索引兜底，否则高并发场景下必然出现两个用户同时绑定同一个联系方式的脏数据。

删除Account记录流程 #

所有操作包裹在同一个数据库事务中执行：

1. 先删除目标Account记录
2. 统计当前用户下，还有多少条Account使用了相同的联系方式值
3. 如果统计值大于0：说明该用户还有其他账号在用这个联系方式，直接提交事务即可，不需要修改绑定表
4. 如果统计值等于0：说明这是该用户下最后一个占用该联系方式的账号，直接删除user_contact_binding中对应的绑定记录后提交事务，此时该联系方式就被释放，其他用户可以正常绑定使用
   不要用定时任务异步扫描释放绑定关系，会存在时间窗口内联系方式被无效占用的问题，同事务内同步判断释放的一致性最强。

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