Postgres的外键深入使用(一)

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

----+----------------------------------+---------------------