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

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

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

1. 建表

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

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

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;

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

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).

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)

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

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!