如何在有逻辑删除的表上建立唯一索引

业务场景：

        我们很多表都会要求做逻辑删除，或者称为假删除，原理就是不使用delete语句删除行数据，而是添加一个删除标记，用update语句代替delete语句，例如下表的 del_flag字段。假如我们又有业务需求，需要对“部门名称”做唯一索引，两者结合就会遇到一些麻烦。

create table sys_dept
(
    dept_id     bigint auto_increment comment '部门id'
        primary key,
    parent_id   bigint      default 0   null comment '父部门id',
    ancestors   varchar(50) default ''  null comment '祖级列表',
    dept_name   varchar(30) default ''  null comment '部门名称',
    order_num   int(4)      default 0   null comment '显示顺序',
    leader      varchar(20)             null comment '负责人',
    phone       varchar(11)             null comment '联系电话',
    email       varchar(50)             null comment '邮箱',
    status      char        default '0' null comment '部门状态（0正常 1停用）',
    del_flag    char        default '0' null comment '删除标志（0代表存在 2代表删除）',
    create_by   varchar(64) default ''  null comment '创建者',
    create_time datetime                null comment '创建时间',
    update_by   varchar(64) default ''  null comment '更新者',
    update_time datetime                null comment '更新时间'
)
    comment '部门表';

需求描述：

在逻辑删除的场景下，对存在部门名称进行唯一限制，对已经标记为删除状态的部门名称不做唯一限制

问题描述：

常规的方案就会存在如下的问题（此处比较简单，不做过多描述）。

                常规方案一：只是对“部门名称”字段做索引。存在问题：如果创建了一个财务部，然后逻辑删除，然后再创建一个财务部，新增时会被唯一索引限制，不满足业务需求。

                常规方案二：对“部门名称”和“删除标志”字段做组合索引。仍然存在问题：如果创建了一个财务部，然后逻辑删除，然后再创建一个财务部，然后再次逻辑删除的时候，还是会被唯一索引限制，因为此时会出现两条相同部门名称、相同删除状态的数据，也不满足业务需求。

解决方案：

方案一：

        如果是在建表阶段遇到该问题，可以对删除字段进行改造，把删除标识的字段类型和主键保持一致，默认为0代表存在；每当删除的时候把该行主键的值回填到删除标识字段，代表已经删除；表结构如下所示。

create table sys_dept
(
    dept_id     bigint auto_increment comment '部门id'
        primary key,
    parent_id   bigint      default 0   null comment '父部门id',
    ancestors   varchar(50) default ''  null comment '祖级列表',
    dept_name   varchar(30) default ''  null comment '部门名称',
    order_num   int(4)      default 0   null comment '显示顺序',
    leader      varchar(20)             null comment '负责人',
    phone       varchar(11)             null comment '联系电话',
    email       varchar(50)             null comment '邮箱',
    status      char        default '0' null comment '部门状态（0正常 1停用）',
    del_flag    bigint      default 0   null comment '删除标志（0代表存在  非0代表删除）',
    create_by   varchar(64) default ''  null comment '创建者',
    create_time datetime                null comment '创建时间',
    update_by   varchar(64) default ''  null comment '更新者',
    update_time datetime                null comment '更新时间',
    constraint sys_dept_dept_name_del_flag_uindex
        unique (dept_name, del_flag) comment '部门名称唯一索引'
)
    comment '部门表';

此时删除的代码就成了：

update sys_dept set del_flag=dept_id where dept_id = 1102;

现在可以正常删除重名，在新增的重名的时候也能被正常限制，如下图所示。满足业务需求。

 方案二：

        额外在添加一个删除标识主键字段，字段类型和主键保持一致，默认为0代表存在；每当删除的时候把该行主键的值回填到删除标识字段，代表已经删除；然后使用“部门名称”字段和“删除标识主键”字段做组合唯一索引，表结构如下所示。

create table sys_dept
(
    dept_id     bigint auto_increment comment '部门id'
        primary key,
    parent_id   bigint      default 0   null comment '父部门id',
    ancestors   varchar(50) default ''  null comment '祖级列表',
    dept_name   varchar(30) default ''  null comment '部门名称',
    order_num   int(4)      default 0   null comment '显示顺序',
    leader      varchar(20)             null comment '负责人',
    phone       varchar(11)             null comment '联系电话',
    email       varchar(50)             null comment '邮箱',
    status      char        default '0' null comment '部门状态（0正常 1停用）',
    del_flag    char        default '0' null comment '删除标志（0代表存在 2代表删除）',
    del_flag_id bigint      default 0   null comment '删除标识主键（0代表存在  非0代表删除）',
    create_by   varchar(64) default ''  null comment '创建者',
    create_time datetime                null comment '创建时间',
    update_by   varchar(64) default ''  null comment '更新者',
    update_time datetime                null comment '更新时间',
    constraint sys_dept_dept_name_del_flag_id_uindex
        unique (dept_name, del_flag_id) comment '部门名称唯一索引'
)
    comment '部门表';

        该方案适用于：

1. 建表规范明确要求逻辑删除字段全库保持一致（很多大公司都有这种限制）
2. 逻辑删除表已经投产后，需要添加唯一索引

此时删除的代码就成了：

update sys_dept set del_flag='1', del_flag_id=dept_id where dept_id = 1103;

设计思想和方案一是一样的，只是做了兼容设计。也是满足业务需求的。