数据库变更历史留存、软删除唯一约束冲突及数据完整性方案咨询

核心冲突场景 #

• 现有表结构：存储文章类文本的主表、存储文本所属分类的主表、独立的变更历史表（字段包含操作人、操作时间、变更备注、关联的文本ID，原配置了指向文本主表的强外键约束）
• 初始痛点：要把删除文本的操作纳入历史记录，但强外键约束不允许在主表文本被物理删除后还留存关联的历史数据，为了不触发外键报错，之前只能连带删除对应文本的全部历史记录，留不住删除操作的痕迹
• 软删除尝试的新卡点：给文本主表加软删除标记后，不物理删除行、只把删除标记置位的方案虽然能保住外键关联，但文本主表的Name字段要求全局唯一，软删除的行还是会占用唯一索引的名额，导致新文本插入时，只要和已标记删除的旧记录同名，就会触发唯一约束报错
• 方案必须覆盖的硬要求：
  • 不管文本主表的记录是被软删还是物理删，历史表都能完整留存全量操作（包括删除）的痕迹
  • 文本主表的Name字段唯一性约束正常生效，不被已删除的无效记录干扰
  • 不破坏数据库本身的数据完整性规则

具体可落地方案 #

1. 最小改动方案：调整唯一索引的过滤规则，适配软删除逻辑 #

这个方案基本不用改现有表的结构，只需要调整索引定义，同时把软删除标记的字段逻辑稍微改下：

• 把原来布尔类型的Deleted标记（值为0/1），换成可空的时间戳字段deleted_at：未删除的有效记录这个字段值为NULL，已执行删除操作的记录，这个字段值写入操作发生的具体时间戳
• 把原来直接建在Name字段上的全表唯一索引，换成带过滤条件的部分唯一索引：只对deleted_at IS NULL的有效记录校验Name唯一性，软删除的记录不纳入唯一性校验范围
  • 支持部分索引的数据库（PostgreSQL、SQLite等），直接创建过滤索引即可：CREATE UNIQUE INDEX uk_text_name_active ON texts(name) WHERE deleted_at IS NULL
  • 不支持部分过滤索引的数据库（如MySQL），把原来的单字段唯一索引换成联合唯一索引，联合字段为(name, deleted_at)：因为未删除的有效记录deleted_at固定为0，同Name的有效记录只会存在一条；软删除的记录deleted_at是不同的时间戳，就算Name重复也不会触发唯一冲突
  • 注意不要在MySQL里继续用固定布尔值的软删标记，否则两个同Name的软删记录会因为联合索引键值完全相同触发唯一冲突，换成可变时间戳的deleted_at字段就能规避这个问题
• 这个方案下，原来的外键约束完全不需要调整：因为软删除只是标记字段，不会物理删除文本主表的行，历史表的外键关联永远有效，删除操作只需要两步：给对应文本行的deleted_at赋值当前时间，再往历史表插一条操作类型为删除的记录即可，完全不会触发外键报错

2. 适配物理删除场景的方案：历史表调整为快照模式 #

如果业务规则明确要求不能在文本主表留存已删除的物理行，那就调整历史表的存储逻辑，从根源上绕开外键约束的限制：

• 去掉历史表上指向文本主表的强外键约束，把关联的文本ID字段改成普通索引，保留关联关系的同时不做数据库层的强校验
• 给历史表补充核心字段的快照存储，比如冗余存当时的文本Name、文本内容、所属分类ID这些核心字段，就算原文本被物理删除，历史记录本身也能完整还原当时的操作上下文，不需要依赖主表的存在
• 执行删除操作的流程：先把当前文本的全量核心字段快照插入历史表，记录操作人、操作时间、删除备注，再物理删除文本主表对应的行
• 注意：这个方案适合历史表数据量较大、不需要频繁关联主表做联表查询的场景，要是团队规则要求所有关联字段必须有强外键保障一致性，就不适合用这个方案

3. 规范分层方案：主表只存有效数据，全量版本下沉到历史表 #

这个是长期维护成本最低的结构，从根源上避免软删除行占用唯一索引名额的问题：

• 文本主表只存储当前生效的、未删除的文本记录，原来的Name全表唯一约束直接保留即可，因为主表根本不存已删除的无效行，完全不会有同名冲突
• 历史表调整为全量版本快照表，表结构包含：原文本ID、版本号、操作类型（新增/修改/删除）、操作人、操作时间、变更备注、对应版本的Name/内容/分类ID快照
• 当执行删除操作时，先把当前版本的文本快照插入历史表，标记操作类型为DELETE，再物理删除文本主表的对应行
• 这个结构下，所有的变更、删除痕迹都完整存在历史表中，主表没有冗余的无效数据，唯一约束的校验逻辑最简单，长期维护成本最低

选型参考 #

• 改动预算最少的话，直接选第一个调整唯一索引的方案，基本不用动业务逻辑，只需要调整索引定义，就能同时解决外键关联和Name唯一性的问题
• 如果要长期维护、不想在主表留无效软删数据，就选第三个分层方案，把所有历史快照下沉到历史表
• 尽量避免直接去掉强外键的方案，会带来历史记录关联悬空的问题，后续排查问题的成本会高很多

内容的提问来源于stack exchange，提问作者Aleksandr Kurilov