PostgreSQL 逻辑删除的表的唯一约束的控制

最新推荐文章于 2026-06-20 16:44:14 发布

原创最新推荐文章于 2026-06-20 16:44:14 发布 · 9k 阅读

1 ·

本内容遵循CC 4.0 BY-SA版权协议

收录于

PostgreSQL 基础

本文介绍了一种在PostgreSQL中实现逻辑删除的同时保持数据唯一性的方法，通过自定义函数和约束确保即使标记为已删除的数据也能保持字段的唯一性。

很多时候，当我们的数据很重要，删除采用的是逻辑删除，仅仅是在记录上指定一个删除标记；这个时候，我们又需要该表的某一字段唯一（未删除的保持唯一，已经有删除标记的行不管），仅仅是采用数据库本身的check unique就不行了。

现在开始讲一种解决方案:

1. 建表

postgres=# create table person(id serial,name character varying,delete_flag integer not null default 0);

2. 查看表结构

postgres=# \d person
                                 Table "public.person"
   Column    |       Type        |                      Modifiers                     
-------------+-------------------+----------------------------------------------
 id          | integer           | not null default nextval('person_id_seq'::regclass)
 name        | character varying | 
 delete_flag | integer           | not null default 0

3. 创建function

CREATE OR REPLACE FUNCTION check_person_name_unique( p_name character varying, flag integer)
  RETURNS boolean AS
$BODY$
declare
	count integer:=0;
begin 
	if(flag=1) then
		return true; 
	end if;
	select count(0) from person where name=p_name and delete_flag=0 into count;
	if count>0 then
		return false;
	elseif count=0 then
		return true;
	end if;
end;
  $BODY$
  LANGUAGE plpgsql VOLATILE;

4. 应用到表上

postgres=# alter table person add CONSTRAINT check_unique CHECK (check_person_name_unique(name, delete_flag));

5. 插入测试数据

postgres=# insert into person(name) values('zhangsan');
INSERT 0 1
postgres=# insert into person(name) values('zhangsan');
ERROR:  new row for relation "person" violates check constraint "check_unique"
DETAIL:  Failing row contains (2, zhangsan, 0).

插入两条相同的违反了唯一约束
6. 现在把记录更新 delete_flag=1;

postgres=# select * from person;
 id |   name   | delete_flag 
----+----------+-------------
  1 | zhangsan |           0
(1 row)

postgres=# update person person set delete_flag=1;
UPDATE 1

postgres=# select * from person;
 id |   name   | delete_flag 
----+----------+-------------
  1 | zhangsan |           1
(1 row)

7. 再插入名字相同的

postgres=# insert into person(name) values('zhangsan');
INSERT 0 1

插入成功
8. 查看数据

postgres=# select * from person;
 id |   name   | delete_flag 
----+----------+-------------
  1 | zhangsan |           1
  3 | zhangsan |           0
(2 rows)

这样就可以保证逻辑删除的记录的唯一性。

标签

#数据库 #postgresql #唯一约束