postgresql----数据库表约束----PRIMARY KEY
2024-08-27 09:57:38
五.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
最新文章
- SCCM 2012 R2安装部署过程和问题(二)
- xcode8让真机测试支持ios8.0以下版本
- Change Eclipse Tooltip's Color in Ubuntu
- Stockbroker Grapevine(floyd)
- A4纸网页打印——宽高设置
- pl/sql programming 06 异常处理
- Websocket协议之php实现
- Cocos2d-x滚动列表具体解释(CCScrollView的使用)
- 【百度地图API】如何判断点击的是地图还是覆盖物?
- 读书笔记 effective c++ Item 25 实现一个不抛出异常的swap
- 创建以mybatis为基础的web项目(1)
- 解决connect() failed (111: Connection refused) while connecting to upstream
- LeetCode--026--删除排序数组中的重复项(java)
- ASP.NET Core 微服务初探[2]:熔断降级之Polly
- 获取map集合中key、value
- XtraBackup的备份原理与应用示例
- ccf--20150303--节日
- MVC字符串转json,ajax接受json返回值
- Linux 编译时内存不足
- Codeforces Round #310 (Div. 1) B. Case of Fugitive(set二分)