当前位置:数据库 > Postgres >>

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            
----+----------------------------------+----------------------------
  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 易做图 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
CopyRight © 2022 站长资源库 编程知识问答 zzzyk.com All Rights Reserved
部分文章来自网络,