很多时候,当我们的数据很重要,删除采用的是逻辑删除,仅仅是在记录上指定一个删除标记;这个时候,我们又需要该表的某一字段唯一(未删除的保持唯一,已经有删除标记的行不管),仅仅是采用数据库本身的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)
这样就可以保证逻辑删除的记录的唯一性。