如何设计支持软删除的唯一索引

背景

业务中通常有需要给表加唯一索引的场景：

1. 官网站点表中，每个站点的url后缀路径需要保证唯一性
2. 市民系统中，每个市民只有一条记录，也就是给身份证字段加唯一索引

但如果这些表支持软删除，且删除后能重复加入原来的记录，例如删除url后缀为’aaa’的站点记录，要能支持后续可以插入另一个后缀也为’aaa’的站点

在有唯一索引的情况下，是插不进去的，因为表中已经有一条后缀为’aaa’的记录，虽然其被逻辑删除，但其唯一索引的字段没有改变

那我搞个联合唯一索引，url_suffix, is_del行得通吗？答案是不行，若只有一条被删除的数据是可以的，但想删除新插入的记录时，会发现删除不了，因为违反了联合唯一索引的约束

本文将介绍应该该场景的两种解决方案

记录删除时间

我们以官网站点表site为例，新增一个字段删除时间 del_time，给url后缀和删除时间加联合唯一索引：url_suffix, del_time

未删除时，del_time等于0，唯一索引中相当于只有url_suffix生效，这样能保证未删除的站点的url后缀唯一

删除某个站点时，将其del_time置为当前时间

update site set is_del = 1 and del_time = now() where id = ${id}

这样能保证：

1. 已删除的站点不会对以后新增相同后缀的站点产生约束，因为其del_time不一样
2. 多个相同后缀的已删除站点之间，也不会产生约束，因为删除时间不一样，del_time就不一样

修改唯一索引字段

若不想新增字段，删除站点记录时可以将其url_suffix字段置为一个不会重复的值，例如该条记录的主键id，这样该条记录就不会约束其他记录，因为id值肯定不同

update site set is_del = 1 and url_suffix = ${id} where id = ${id}

作为被逻辑删除的数据，抹去其中一个字段的数据一般问题不大，如果想保留，可以将url_suffix字段更新为${id}_${url_suffix}的形式。因为前缀id唯一，则该结构的任意值也唯一，既保留了原始数据，也不会约束未来要插入的记录

update site set is_del = 1 and url_suffix = ${id}_${url_suffix} where id = ${id}

总结

本文总结了如何设计支持软删除的唯一索引，可以记录删除时间，也可以删除时修改唯一索引的值，使得其被删除后不再约束其他记录