如何配置数据库约束 使组合主键仅限制未删除记录的组合唯一性

原有方案的潜在问题 #

• 主键引入业务标记字段deleted不符合常规数据库范式设计，后续维护成本更高
• 删除操作需要先查询当前user_id + well_id组合下的最大deleted值再自增，多了一次查询开销，高并发场景下容易出现竞态冲突
• 若出现异常重试等场景，仍有概率触发主键冲突

推荐最优方案：使用部分（条件）唯一索引 #

主流数据库（PostgreSQL、MySQL 8.0+、SQL Server 2008+）都支持带过滤条件的唯一索引，完全匹配你的需求，不需要修改原有主键逻辑，也不需要调整删除操作的核心逻辑：
直接执行以下DDL创建索引即可：

CREATE UNIQUE INDEX idx_unique_active_user_well 
ON 你的表名 (user_id, well_id) 
WHERE deleted = 0;

该方案的优势：

• 只有deleted = 0的未删除记录会进入索引受唯一性约束，deleted > 0的已删除记录完全不受影响，和你要求的规则100%匹配
• 删除操作不需要额外查询，直接把deleted设为任意大于0的值即可，甚至可以直接设为删除时的毫秒时间戳，额外保留删除时间信息
• 索引体积更小，只有未删除的记录会被索引，查询未删除记录的性能也会更高

兼容老版本数据库的替代方案 #

如果你使用的是不支持部分唯一索引的低版本数据库（如MySQL 5.x），可以用计算列+唯一索引的方案实现同等效果：

1. 新增一个存储类生成列，仅未删除记录会生成唯一标识，已删除记录值为NULL（唯一索引允许多个NULL值存在，不会触发冲突）
2. 给该生成列创建唯一索引
   示例代码（MySQL 5.x）：

ALTER TABLE 你的表名 
ADD COLUMN active_unique_flag VARCHAR(255) 
GENERATED ALWAYS AS (IF(deleted = 0, CONCAT(user_id, '_', well_id), NULL)) STORED;

CREATE UNIQUE INDEX idx_unique_active_user_well 
ON 你的表名 (active_unique_flag);

原有方案的优化思路 #

如果因为业务限制必须保留(user_id, well_id, deleted)作为组合主键，不需要自己维护deleted的自增逻辑，直接将deleted赋值为删除时的13位毫秒时间戳即可，几乎不会出现重复冲突，省去了查询最大deleted值的开销。

内容的提问来源于stack exchange，提问作者MC Hammerabi