文档章节

PostgreSQL的外键深入使用

kenyon_君羊
 kenyon_君羊
发布于 2013/06/01 21:06
字数 1218
阅读 10277
收藏 24

有开发同事问及postgresql外键的用法,这里普及一下。外键是一个很基础的概念,使用得当可以对事务的一致性有很好的保障,方法上和Oracle是很接近的,作用很简单地说就是保证子表的数据都能在主表中找到,可保证数据一致性。

建立主表

postgres=# create table t_parent(
postgres(# id serial primary key,
postgres(# vname varchar(32),
postgres(# ctime timestamp without time zone);
NOTICE:  CREATE TABLE will create implicit sequence "t_parent_id_seq" for serial column "t_parent.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "t_parent_pkey" for table "t_parent"
CREATE TABLE


建立子表

postgres=# create table t_child(
postgres(# cid int4,
postgres(# vname varchar(32));
CREATE TABLE


查看表外键

postgres=# \d+ t_child
                               Table "public.t_child"
 Column |         Type          | Modifiers | Storage  | Stats target | Description
--------+-----------------------+-----------+----------+--------------+-------------
 cid    | integer               |           | plain    |              |
 vname  | character varying(32) |           | extended |              |
Foreign-key constraints:
    "t_child_fk" FOREIGN KEY (cid) REFERENCES t_parent(id)
Has OIDs: no

在PGADMINIII中查看
CREATE TABLE t_child
(
  cid integer,
  vname character varying(32),
  CONSTRAINT t_child_fk FOREIGN KEY (cid)
      REFERENCES t_parent (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);
ALTER TABLE t_child
  OWNER TO postgres;


建立外键关联,如果子表有父表没有的数据,会报错
postgres=# alter table t_child add constraint t_child_fk foreign key(cid) references t_parent (id) ;
ALTER TABLE

--另一种情况,需要先清理数据
postgres=# alter table t_child add constraint t_child_fk foreign key(cid) references t_parent (id) ;
ERROR:  insert or update on table "t_child" violates foreign key constraint "t_child_fk"
DETAIL:  Key (cid)=(100001) is not present in table "t_parent".


查看外键的关联关系

postgres=# SELECT
postgres-#     tc.constraint_name, tc.table_name, kcu.column_name,
postgres-#     ccu.table_name AS foreign_table_name,
postgres-#     ccu.column_name AS foreign_column_name,
postgres-#     tc.is_deferrable,tc.initially_deferred
postgres-# FROM
postgres-#     information_schema.table_constraints AS tc
postgres-#     JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name
postgres-#     JOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name
postgres-# WHERE constraint_type = 'FOREIGN KEY' AND tc.table_name='t_child';
 constraint_name | table_name | column_name | foreign_table_name | foreign_column_name | is_deferrable | initially_deferred
-----------------+------------+-------------+--------------------+---------------------+---------------+--------------------
 t_child_fk      | t_child    | cid         | t_parent           | id                  | NO            | NO
(1 row)

外键数据生成
postgres=# insert into t_parent select generate_series(1,100000),md5(random()::text),clock_timestamp();
INSERT 0 100000

postgres=# insert into t_child select id,md5(random()::text) from t_parent;
INSERT 0 100000

postgres=# select * from t_parent limit 10;
 id |              vname               |           ctime            
----+----------------------------------+----------------------------
  2 | f12c9b7d21f467a6c47b5adca5a5478e | 2013-05-20 20:51:08.678242
  3 | ce758f15428d56be00ba5b0834daa5af | 2013-05-20 20:51:08.678284
  4 | 55892bd9a81db1566c7fefb3e459dcd6 | 2013-05-20 20:51:08.678303
  5 | 5c9dabb81782953fdfea3da0d7bafdbb | 2013-05-20 20:51:08.678322
  6 | e5358f0c23d9042e599aa8d03b6b8944 | 2013-05-20 20:51:08.67834
  7 | e51c3ab198d605699de5472dc7589712 | 2013-05-20 20:51:08.678357
  8 | db8c0b2f7ad6579594f79abf2828f70e | 2013-05-20 20:51:08.678376
  9 | 904630d3dcab4308edea4bed5f6b556d | 2013-05-20 20:51:08.678394
 10 | 1c419398ac492b16be8a252a9c8e28ba | 2013-05-20 20:51:08.678411
 11 | b774007d756a6c4b7c54d3854eb964b7 | 2013-05-20 20:51:08.678429
(10 rows)


外键对数据导入的影响测试

postgres=# \timing
Timing is on.
postgres=# copy t_child(cid,vname) to '/home/postgres/t_child.bak';
COPY 100000
Time: 207.030 ms
postgres=# truncate table t_child;
TRUNCATE TABLE
Time: 43.775 ms
postgres=# copy t_child(cid,vname) from '/home/postgres/t_child.bak';
COPY 100000
Time: 10325.357 ms
postgres=# truncate table t_child;
TRUNCATE TABLE
Time: 16.749 ms
postgres=# alter table t_child drop constraint t_child_fk;
ALTER TABLE
Time: 26.552 ms
postgres=# copy t_child(cid,vname) from '/home/postgres/t_child.bak';
COPY 100000
Time: 755.239 ms
postgres=#

可以看到加了外键后对数据的导入影响很大,这里只是测试了10W数据的COPY导入,数据量再大一点差别更明显,所以大数据的导入请先去掉各种约束,这对其他DB也适用。


UPDATE和DELETE的外键属性
上面建的外键默认是MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION,除了NO ACTION,还有cascade/restrict这两种常用的。
no action和restrict对于操作都会检查,如果不符合约束则会报ERROR并退出,数据还是不变,唯一的区别是no action可以设置约束延迟生效,而restrict不允许,见http://my.oschina.net/Kenyon/blog/126360
cascade则是级联的意思,如删除父表数据时子表也存在则会级联删除
cascade示例:

postgres=# alter table t_child add constraint t_child_fk foreign key(cid) references t_parent (id) match simple on update cascade on delete cascade;
ALTER TABLE


postgres=# select * from t_child where cid = 100003;
 cid | vname
-----+-------
(0 rows)

postgres=# select * from t_parent where id = 100003;
 id | vname | ctime
----+-------+-------
(0 rows)

postgres=# update t_parent set id = 100003 where id = 100002;
UPDATE 1
postgres=# select * from t_parent where id = 100003;
   id   |              vname               |           ctime           
--------+----------------------------------+----------------------------
 100003 | 20e9c1b966bc9fc133339bad7d374dd8 | 2013-05-20 20:51:08.677156
(1 row)

postgres=# select * from t_child where cid = 100003;
  cid   |              vname              
--------+----------------------------------
 100003 | 9fd9b9d977abcba5f8b38658b4116985
(1 row)


这对delete是一样的,主表数据被删,关联子表数据也被删

同样,匹配的方式也有三种match simple/match full/match partition,其实是两种
simple(默认)
full
partition(功能还未完成)
simple与full的区别是simple允许多字段外键的部分字段数据为Null,而full一般是不允许外键字段数据为Null,除非该外键的所有字段都为Null。示例:

postgres=# create table t_p(id1 int,id2 int);
CREATE TABLE
postgres=# create table t_c(id1 int,id2 int);
CREATE TABLE
postgres=# insert into t_p values(1,2),(1,3),(2,3);
INSERT 0 3
postgres=# alter table t_p add constraint dd unique(id1,id2);
NOTICE:  ALTER TABLE / ADD UNIQUE will create implicit index "dd" for table "t_p"
ALTER TABLE
postgres=# alter table t_c add constraint fk_c foreign key(id1,id2) references t_p(id1,id2) match full;
ALTER TABLE
postgres=# insert into t_c values(1,2);
INSERT 0 1
postgres=# insert into t_c values(null,null);
INSERT 0 1
postgres=# insert into t_c values(1,null);
ERROR:  insert or update on table "t_c" violates foreign key constraint "fk_c"
DETAIL:  MATCH FULL does not allow mixing of null and nonnull key values.

--另外一种模式
postgres=# alter table t_c drop constraint fk_c;
ALTER TABLE
postgres=# alter table t_c add constraint fk_c foreign key(id1,id2) references t_p(id1,id2) match simple;
ALTER TABLE
postgres=# insert into t_c values(1,2);
INSERT 0 1
postgres=# insert into t_c values(1,null);
INSERT 0 1
postgres=# insert into t_c values(null,null);
INSERT 0 1
可以看到插空值入有明显的区别。

© 著作权归作者所有

共有 人打赏支持
kenyon_君羊
粉丝 499
博文 170
码字总数 121714
作品 0
杭州
其他
私信 提问
加载中

评论(4)

乌龟壳
乌龟壳
取消外键导入速度确实加快了,但最后还要应用回外键,所以总的时间要把两者加起来才可以。
从测试数据看好像没必要取消外键。
kenyon_君羊
kenyon_君羊

引用来自“InBi”的评论

好东西,就是看得太累,怎么没有语法高亮/

有些不是加粗了么
InBi
InBi
好东西,就是看得太累,怎么没有语法高亮/
mark35
mark35
关系型数据库中FK是保证关系完整的一个重要功能
[笔记]将系统的数据库从MySQL 5.5迁移到PostgreSQL 9.1

环境 Windows Server 2003 x64 简体中文, MySQL 5.5 (UTF8编码), PostgreSQL 9.1.4-1 (UTF8编码) Spring 3.0.7, Struts 2.3.4, Hibernate 3.5.5 从MySQL迁移到PostgreSQL ------------------......

leeoo
2012/07/22
0
3
PostgreSQL 11 发布:JIT、存储过程事务,并行性能提升

PostgreSQL 11 发布了,PostgreSQL 11 带来了整体性能的改进,具有与超大型数据库和高计算工作负载相关的特定增强功能。此外,PostgreSQL 11 对表分区系统进行了重大改进,存储过程中支持事务...

h4cd
2018/10/19
7.2K
22
PostgreSQL学习手册(十三) 系统表

一、pg_class: 该系统表记录了数据表、索引(仍然需要参阅pg_index)、序列、视图、复合类型和一些特殊关系类型的元数据。注意:不是所有字段对所有对象类型都有意义。 名字 类型 引用 描述 re...

长平狐
2012/08/27
166
0
PostgreSQL学习手册(十三) 系统表

一、pg_class: 该系统表记录了数据表、索引(仍然需要参阅pg_index)、序列、视图、复合类型和一些特殊关系类型的元数据。注意:不是所有字段对所有对象类型都有意义。 名字 类型 引用 描述 re...

长平狐
2012/09/06
80
0
PostgreSQL 与其他数据库的比较(标题党)

这是 PostgreSQL 官网 FAQ 中关于功能和性能与其他数据库的比较说明。 功能: PostgreSQL 提供了多数数据库系统所需的基本功能,例如事务处理、子查询、触发器、视图、外键引用完整性、乐观锁...

鉴客
2011/04/24
934
3

没有更多内容

加载失败,请刷新页面

加载更多

PHP is_numeric 检测变量是否为数字或数字字符串

bool is_numeric ( mixed $var ) 如果 var 是数字和数字字符串则返回 TRUE,否则返回 FALSE。 For example 1: <?php $v = is_numeric ('58635272821786587286382824657568871098287278......

james_laughing
15分钟前
1
0
聊聊flink的NetworkEnvironmentConfiguration

序 本文主要研究一下flink的NetworkEnvironmentConfiguration NetworkEnvironmentConfiguration flink-1.7.2/flink-runtime/src/main/java/org/apache/flink/runtime/taskmanager/NetworkEnv......

go4it
今天
4
0
极路由4刷OpenWrt(LEDE)

申请开发者模式 登录路由器后台 进入插件中间 选中路由器信息 申请开发者权限 安装开发者插件 刷 Bootloader 下载 极路由4 的 Breed 通过SSH登录路由器 (必须安装开发者插件,端口 1022 账号...

dingdayu
今天
3
0
浅淡个人学习嵌入式Linux过程

我专业是电子信息工程,在初入大学的时候,我们的班主任便要我们多多去了解一些关于电子方面的知识。后来我了解到了嵌入式,继而了解到了嵌入式Linux。其实我们学习linux差不多就学习linux内...

linux-tao
今天
7
0
SpringBoot使用GraphQL简单学习-1

官网 一、GraphQL简介 1.GraphQL是什么? GraphQL 既是一种用于 API 的查询语言也是一个满足你数据查询的运行时。 GraphQL 对你的 API 中的数据提供了一套易于理解的完整描述,使得客户端能够...

wind2012
今天
7
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部