postgresql—-数据库表约束—-PRIMARY KEY详解数据库

主键可以是单个字段，也可以是多个字段的组合。主键约束其实是UNIQUE和NOT NULL约束的组合，即主键必须是唯一，且各字段都是NOT NULL的。

1.创建测试表

create table tbl_primary( 

a int not null, 

b int, 

c int, 

constraint pk_tbl_primary_a_b primary key (a,b) 

);

其中(a,b)是组合主键，即a和b的组合必须是唯一的，且a是not null,b也是not null的，虽然表定义中没有明确b是not null的，但是因为b是主键的一部分，增加主键时会给b增加not null约束。

测试例

test=# insert into tbl_primary (a,b,c) values (1,1,1); 

INSERT 0 1 

test=# insert into tbl_primary (a,b,c) values (1,2,1); 

INSERT 0 1 

test=# insert into tbl_primary (a,b,c) values (1,1,1); 

ERROR: duplicate key value violates unique constraint "pk_tbl_primary_a_b" 

DETAIL: Key (a, b)=(1, 1) already exists. 

test=# insert into tbl_primary (a,c) values (1,5); 

ERROR: null value in column "b" violates not-null constraint 

DETAIL: Failing row contains (1, null, 5). 

 

2.删除主键约束

test=# alter table tbl_primary drop constraint pk_tbl_primary_a_b ; 

ALTER TABLE

3.增加主键约束

向已存在的表中增加主键约束就必须考虑已存在的数据不是唯一的，或者有可能是NULL，此时增加主键约束就会失败，所以增加主键约束之前先删除这些脏数据。

如果你看了前一节增加唯一约束前删除脏数据，那么这一节简直就是小kiss。

对主键来说脏数据包括2个部分：NULL和重复数据，删除NULL数据比较简单，使用下面的SQL语句即可

test=# delete from tbl_primary where a is null or b is null;

NULL数据删除后，下面主要讲如何删除重复数据，和UNIQUE处理方式相同，有两种处理方式：

一、将重复数据删除到只剩一条

二、将重复数据全部删除

方式一  将重复数据删除到只剩一条

第一步：利用表的oids属性，修改表的属性

test=# alter table tbl_primary set with oids; 

ALTER TABLE

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

test=# alter table tbl_primary drop constraint pk_tbl_primary_a_b ; 

ALTER TABLE 

test=# delete from tbl_primary where a is null or b is null; 

DELETE 0 

test=# insert into tbl_primary (a,b) values (1,1),(1,1),(1,1),(2,2),(2,2); 

INSERT 0 5 

test=# select oid,* from tbl_primary ; 

 oid | a | b | c 

-------+---+---+------ 

 16423 | 1 | 1 | 1 

 16424 | 1 | 2 | 1 

 16425 | 1 | 1 | NULL 

 16426 | 1 | 1 | NULL 

 16427 | 1 | 1 | NULL 

 16428 | 2 | 2 | NULL 

 16429 | 2 | 2 | NULL 

(7 rows)

第三步：查询重复数据中最小oid

test=# select min(oid) from tbl_primary group by a,b; 

 min 

------- 

 16428 

 16423 

 16424 

(3 rows)

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

test=# select oid,* from tbl_primary where oid not in (select min(oid) from tbl_primary group by a,b); 

 oid | a | b | c 

-------+---+---+------ 

 16425 | 1 | 1 | NULL 

 16426 | 1 | 1 | NULL 

 16427 | 1 | 1 | NULL 

 16429 | 2 | 2 | NULL 

(4 rows)

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

将上面语句中的SELECT替换成DELETE即可

test=# delete from tbl_primary where oid not in (select min(oid) from tbl_primary group by a,b); 

DELETE 4 

test=# select oid,* from tbl_primary ; 

 oid | a | b | c 

-------+---+---+------ 

 16423 | 1 | 1 | 1 

 16424 | 1 | 2 | 1 

 16428 | 2 | 2 | NULL 

(3 rows)

第六步：增加主键

test=# alter table tbl_primary add constraint pk_tbl_primary_a_b primary key(a,b); 

ALTER TABLE

方式二  将重复数据全部删除

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

alter table tbl_primary drop constraint pk_tbl_primary_a_b ; 

ALTER TABLE 

test=# delete from tbl_primary; 

DELETE 8 

test=# insert into tbl_primary (a,b) values (1,1),(1,1),(1,1),(2,2),(2,2); 

INSERT 0 5 

test=# select * from tbl_primary ; 

 a | b | c 

---+---+------ 

 1 | 1 | NULL 

 1 | 1 | NULL 

 1 | 1 | NULL 

 2 | 2 | NULL 

 2 | 2 | NULL 

(5 rows)

第二步：查询重复的数据

test=# select a,b from tbl_primary group by a,b having count(*) 1; 

 a | b 

---+--- 

 2 | 2 

 1 | 1 

(2 rows)

第三步：查询所有的重复数据

test=# select * from tbl_primary where exists (select null from (select a,b from tbl_primary group by a,b having count(*) 1) tbl_temp where tbl_primary.a=tbl_temp.a and tbl_primary.b=tbl_temp.b); 

 a | b | c 

---+---+------ 

 1 | 1 | NULL 

 1 | 1 | NULL 

 1 | 1 | NULL 

 2 | 2 | NULL 

 2 | 2 | NULL 

(5 rows)

第四步：删除所有的重复数据

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

test=# delete from tbl_primary where exists (select null from (select a,b from tbl_primary group by a,b having count(*) 1) tbl_temp where tbl_primary.a=tbl_temp.a and tbl_primary.b=tbl_temp.b); 

DELETE 5

第五步：增加主键约束

test=# alter table tbl_primary add constraint pk_tbl_primary_a_b primary key(a,b); 

ALTER TABLE

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