逻辑删除

一般情况下，出于数据、安全等方面的考虑，在设计一个系统时，我们会用逻辑删除来代替物理删除：

在表中添加一个 boolean 字段以标识对应记录是否被删除

如果此时需要在记录上创建一个唯一索引，boolean 类型字段便不再满足需求，会遇到无法多次删除的问题：

-- 创建测试表
create table t_user (
                        user_id int primary key ,
                        user_name varchar(20) not null ,
                        age int,
                        delete_flag boolean default false
);
-- 创建唯一索引
create unique index if not exists IDX_T_USER__USER_NAME_DELETE_FLAG on t_user(user_name, delete_flag);
-- 插入测试数据
insert into t_user (user_id, user_name, age, delete_flag) VALUES (1, 'user1', 20, false);
-- 逻辑删除测试数据
update t_user set delete_flag=true where user_id=1;
-- 插入唯一键相同的另一条数据
insert into t_user (user_id, user_name, age, delete_flag) VALUES (2, 'user1', 30, false);
-- 删除第二次插入的数据，此时将报唯一键冲突
update t_user set delete_flag=true where user_id=2;

此时，我们可以按如下方案解决：

方案	描述	优点
修改逻辑删除标识字段的数据类型	将逻辑删除标识位的数据类型设置为与主键相同,删除时用主键填充该字段，应用中使用空/非空判断数据是否删除	无需增加新字段，已有查询修改较为容易

而在PostgreSQL中，我们还可以通过***部分索引***解决。

部分索引 (opens new window) 是PostgreSQL在标准SQL之上提供的特性，支持在创建索引的时候加上where条件，只对一部分数据创建索引。

我们可以只对 delete_flag = false 的数据创建唯一索引，即可满足需求。

-- 创建测试表
create table t_user (
                        user_id int primary key ,
                        user_name varchar(20) not null ,
                        age int,
                        delete_flag boolean default false
);
-- 创建唯一索引
create unique index if not exists IDX_T_USER__USER_NAME on t_user(user_name) where delete_flag = false;
-- 插入测试数据
insert into t_user (user_id, user_name, age, delete_flag) VALUES (1, 'user1', 20, false);
-- 逻辑删除测试数据
update t_user set delete_flag=true where user_id=1;
-- 插入唯一键相同的另一条数据
insert into t_user (user_id, user_name, age, delete_flag) VALUES (2, 'user1', 30, false);
-- 删除第二次插入的数据，此时不会报唯一键冲突
update t_user set delete_flag=true where user_id=2;