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)
这样就可以保证逻辑删除的记录的唯一性。

猜你在找的Postgre SQL相关文章