有开发同事问及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中查看 |
建立外键关联，如果子表有父表没有的数据，会报错
|
postgres=# alter table t_child add constraint t_child_fk
foreign key(cid) references t_parent (id) ; ALTER TABLE
--另一种情况，需要先清理数据 |
查看外键的关联关系
|
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;
postgres=# select * from t_parent limit 10; |
外键对数据导入的影响测试
|
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_parent where id = 100003;
postgres=# update t_parent set id = 100003 where id =
100002;
postgres=# select * from t_child where cid = 100003; 这对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.
--另外一种模式 |
京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有