1. 云图网首页
  2. 技术专区
  3. 数据库

postgresql—-数据库表约束—-UNIQUE详解数据库

• 数据库

 

四、UNIQUE —- 唯一约束

唯一键可以是单个字段，也可以是多个字段的组合，设置唯一约束后，INSERT或UPDATE时如果表中唯一键字段中已存在该数据，则拒绝该行数据的INSERT或UPDATE。但是数据库中NULL并不等于NULL，所以唯一键中如果没有NOT NULL约束，则可以在唯一键中INSERT或UPDATE任意多个NULL。

1.创建测试表

唯一约束为组合键(a,b),即a和b的组合必须是唯一的。

create table tbl_unique( 
a int not null, 
b int, 
c varchar(10) not null default 'catch u', 
constraint uk_tbl_unique_a_b unique(a,b) 
);

向tbl_unique表中写入数据(1,,1,’test’)

test=# insert into tbl_unique (a,b,c) values(1,1,'test'); 
INSERT 0 1

再次写入(a,b)组合(1,1)时，则会返回错误。

test=# insert into tbl_unique (a,b,c) values(1,1,'u see'); 
ERROR:  duplicate key value violates unique constraint "uk_tbl_unique_a_b" 
DETAIL:  Key (a, b)=(1, 1) already exists.

那么唯一键中出现NULL呢？唯一键中可以写入任意多个NULL！

test=# insert into tbl_unique (a) values(2); 
INSERT 0 1 
test=# insert into tbl_unique (a) values(2); 
INSERT 0 1 
test=# insert into tbl_unique (a) values(2); 
INSERT 0 1 
test=# /pset null 'NULL' 
Null display is "NULL". 
test=# select * from tbl_unique ; 
 a |  b   |    c     
---+------+--------- 
 1 |    1 | test 
 2 | NULL | catch u 
 2 | NULL | catch u 
 2 | NULL | catch u 
(4 rows)

2.唯一键约束删除

test=# alter table tbl_unique drop constraint uk_tbl_unique_a_b ; 
ALTER TABLE

3.唯一键约束增加

如果你想向表中增加唯一约束，必须要考虑表中已存在的数据可能存在重复数据。重复的数据有两种理解方式：

方式一：严格意义上的唯一，NULL不等于NULL，即(1,NULL)和(1,NULL)不是重复数据。

方式二：非严格意义上的唯一，NULL等于NULL，即(1,NULL)和(1,NULL)是重复数据。

 

所以向表中增加唯一约束必须要删除这些重复数据，或者将重复数据删除到唯一。

情况一：删除严格意义上的重复

第一步：清空测试表，写入一些测试数据。

 

test=# delete from tbl_unique ; 
DELETE 4 
test=# insert into tbl_unique (a,b) values (1,1),(1,1),(1,1); 
INSERT 0 3 
test=# insert into tbl_unique (a) values (2),(2),(2); 
INSERT 0 3 
test=# select * from tbl_unique ; 
 a |  b   |    c     
---+------+--------- 
 1 |    1 | catch u 
 1 |    1 | catch u 
 1 |    1 | catch u 
 2 | NULL | catch u 
 2 | NULL | catch u 
 2 | NULL | catch u 
(6 rows)

 

从结果中看，严格意义上的唯一有1个(1,1,’catch u’)和3个(2,NULL,’catch u’)，删除重复数据即是要删除所有的(1,1,’catch u’)。这种情况下只要使用下面的语句删除即可。

delete from tbl_unique where a= 1 and b = 1;

但是如果表中存在成千上万个这种重复数据，这么一条一条的删除岂不显得低级？！

第二步：查询(a,b)存在重复的数据

test=# select a,b from tbl_unique where a is not null and b is not null group by a,b having count(*) > 1; 
 a | b  
---+--- 
 1 | 1 
(1 row)

第三步：查询所有(a,b)重复的数据

test=# select * from tbl_unique where exists(select null from (select a,b from tbl_unique where a is not null and b is not null group by a,b having count(*) > 1)tbl_temp where tbl_temp.a=tbl_unique.a and tbl_temp.b=tbl_unique.b) ; 
 a | b |    c     
---+---+--------- 
 1 | 1 | catch u 
 1 | 1 | catch u 
 1 | 1 | catch u 
(3 rows)

第四步：删除所有(a,b)重复的数据

把上面的语句中select *替换成 delete就可以了。

test=# delete from tbl_unique where exists(select null from (select a,b from tbl_unique where a is not null and b is not null group by a,b having count(*) > 1)tbl_temp where tbl_temp.a=tbl_unique.a and tbl_temp.b=tbl_unique.b) ; 
DELETE 3 
test=# select * from tbl_unique ; 
 a |  b   |    c     
---+------+--------- 
 2 | NULL | catch u 
 2 | NULL | catch u 
 2 | NULL | catch u 
(3 rows)

第五步：增加唯一约束

test=# alter table tbl_unique add constraint uk_tbl_unique_a_b unique (a,b); 
ALTER TABLE

 

情况二：删除非严格意义重复数据

第一步：删除约束，清空数据，写入测试数据

test=# alter table tbl_unique drop constraint uk_tbl_unique_a_b ; 
ALTER TABLE 
test=# delete from tbl_unique ; 
DELETE 3 
test=# insert into tbl_unique (a,b) values (1,1),(1,1),(1,1); 
INSERT 0 3 
test=# insert into tbl_unique (a) values (2),(2),(2); 
INSERT 0 3 
test=# select * from tbl_unique ; 
 a |  b   |    c     
---+------+--------- 
 1 |    1 | catch u 
 1 |    1 | catch u 
 1 |    1 | catch u 
 2 | NULL | catch u 
 2 | NULL | catch u 
 2 | NULL | catch u 
(6 rows)

非严格意义上该表中的数据全部是重复数据，和情况一比只需要把NOT NULL过滤条件去掉即可。

第二步：查询(a,b)重复数据

test=# select a,b from tbl_unique group by a,b having count(*) > 1; 
 a |  b    
---+------ 
 2 | NULL 
 1 |    1 
(2 rows)

 

第三步：查询所有(a,b)重复数据

test=# select * from tbl_unique where exists(select null from (select a,b from tbl_unique group by a,b having count(*) > 1)tbl_temp where (tbl_temp.a=tbl_unique.a and tbl_temp.b=tbl_unique.b) or (tbl_temp.a is null and tbl_unique.a is null) or (tbl_temp.b is null and tbl_unique.b is null)) ; 
 a |  b   |    c     
---+------+--------- 
 1 |    1 | catch u 
 1 |    1 | catch u 
 1 |    1 | catch u 
 2 | NULL | catch u 
 2 | NULL | catch u 
 2 | NULL | catch u 
(6 rows)

第四步：删除所有(a,b)重复数据

同样把上面语句的select * 替换成delete即可。

test=# delete from tbl_unique where exists(select null from (select a,b from tbl_unique group by a,b having count(*) > 1)tbl_temp where (tbl_temp.a=tbl_unique.a and tbl_temp.b=tbl_unique.b) or (tbl_temp.a is null and tbl_unique.a is null) or (tbl_temp.b is null and tbl_unique.b is null)) ; 
DELETE 6 
test=# select * from tbl_unique ; 
 a | b | c  
---+---+--- 
(0 rows)

第五步：增加唯一键约束

test=# alter table tbl_unique add constraint uk_tbl_unique_a_b unique (a,b); 
ALTER TABLE

如果表中没有主键或NOT NULL的唯一键，那么可以利用表的OID属性，将表的oid列显示出来，该列类似主键的功能。利用该列，可以将重复数据删除到只剩一条，先使用下面的SQL语句，修改表的属性。

test=# alter table tbl_unique set with oids; 
ALTER TABLE

 

情况三：将严格意义上重复数据删除到只有一条

第一步：删除表约束，清空表，写入测试数据

test=# alter table tbl_unique drop constraint uk_tbl_unique_a_b ; 
ALTER TABLE 
test=# delete from tbl_unique ; 
DELETE 0 
test=# insert into tbl_unique (a,b) values (1,1),(1,1),(1,1); 
INSERT 0 3 
test=# insert into tbl_unique (a) values (2),(2),(2); 
INSERT 0 3 
test=# select oid,* from tbl_unique ; 
  oid  | a |  b   |    c     
-------+---+------+--------- 
 16399 | 1 |    1 | catch u 
 16400 | 1 |    1 | catch u 
 16401 | 1 |    1 | catch u 
 16402 | 2 | NULL | catch u 
 16403 | 2 | NULL | catch u 
 16404 | 2 | NULL | catch u 
(6 rows)

严格意义上的重复数据是3条(1,1,’catch u’)，现在要将三条的重复数据，删除到只剩一条。

 

第二步：查询重复数据的最小oid

test=# select min(oid) from tbl_unique where a is not null and b is not null group by a,b; 
  min   
------- 
 16399 
(1 row)

 

第三步：查询oid不是最小的重复数据

test=# select oid,* from tbl_unique where oid not in(select min(oid) from tbl_unique where a is not null and b is not null group by a,b) and a is not null and b is not null; 
  oid  | a | b |    c     
-------+---+---+--------- 
 16400 | 1 | 1 | catch u 
 16401 | 1 | 1 | catch u 
(2 rows)

 

第四步：删除oid不是最小的重复数据

把上面的SQL语句中select替换成delete即可。

test=# delete from tbl_unique where oid not in(select min(oid) from tbl_unique where a is not null and b is not null group by a,b) and a is not null and b is not null; 
DELETE 2 
test=# select oid,* from tbl_unique ; 
  oid  | a |  b   |    c     
-------+---+------+--------- 
 16399 | 1 |    1 | catch u 
 16402 | 2 | NULL | catch u 
 16403 | 2 | NULL | catch u 
 16404 | 2 | NULL | catch u 
(4 rows)

 

第五步：增加唯一键约束

test=# alter table tbl_unique add constraint uk_tbl_unique_a_b unique (a,b); 
ALTER TABLE

 

情况四：将非严格意义上重复数据删除到只有一条

第一步：删除唯一约束，清空表，写入测试数据

test=# alter table tbl_unique drop constraint uk_tbl_unique_a_b ; 
ALTER TABLE 
test=# delete from tbl_unique ; 
DELETE 4 
test=# insert into tbl_unique (a,b) values (1,1),(1,1),(1,1); 
INSERT 0 3 
test=# insert into tbl_unique (a) values (2),(2),(2); 
INSERT 0 3 
test=# select oid,* from tbl_unique ; 
  oid  | a |  b   |    c     
-------+---+------+--------- 
 16407 | 1 |    1 | catch u 
 16408 | 1 |    1 | catch u 
 16409 | 1 |    1 | catch u 
 16410 | 2 | NULL | catch u 
 16411 | 2 | NULL | catch u 
 16412 | 2 | NULL | catch u 
(6 rows)

第二步：查询重复数据的最小oid

test=# select min(oid) from tbl_unique group by a,b; 
  min   
------- 
 16410 
 16407 
(2 rows)

第三步：查询oid不是最小的重复数据

test=# select oid,* from tbl_unique where oid not in(select min(oid) from tbl_unique group by a,b); 
  oid  | a |  b   |    c     
-------+---+------+--------- 
 16408 | 1 |    1 | catch u 
 16409 | 1 |    1 | catch u 
 16411 | 2 | NULL | catch u 
 16412 | 2 | NULL | catch u 
(4 rows)

第四步：删除oid不是最小的重复数据

把上面的SQL语句中select替换成delete即可。 

test=# delete from tbl_unique where oid not in(select min(oid) from tbl_unique group by a,b); 
DELETE 4 
test=# select oid,* from tbl_unique ; 
  oid  | a |  b   |    c     
-------+---+------+--------- 
 16407 | 1 |    1 | catch u 
 16410 | 2 | NULL | catch u 
(2 rows)

第五步：增加唯一键约束

test=# alter table tbl_unique add constraint uk_tbl_unique_a_b unique (a,b); 
ALTER TABLE

 

原创文章，作者：ItWorker，如若转载，请注明出处：https://blog.ytso.com/4886.html

赞 (0)
0
上一篇 2021年7月16日
下一篇 2021年7月16日

相关推荐

发表回复

登录后才能评论