postgres的外键深入使用 有开发同事问及postgresql外键的用法，这里普及一下。外键是一个很基础的概念，使用得当可以对事务的一致性有很好的保障，方法上和oracle是很接近的，作用很简单地说就是保证子表的数据都能在主表中找到，可保证数据一致性。 建立主
postgres的外键深入使用
有开发同事问及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这两种常用的。
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 可以看到插空值入有明显的区别。