Postgres的外键深入使用

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 可以看到插空值入有明显的区别。