Postgres的外键深入使用-mysql教程-PHP中文网

首页

数据库

mysql教程

Postgres的外键深入使用

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 07, 2016 pm 02:59 PM

使用外键深入

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"

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

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';

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

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

声明

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系admin@php.cn

如何在Go中使用命名管道？May 11, 2023 pm 04:22 PM

命名管道是一种在操作系统中相对比较低级的进程通信方式，它是一种以文件为中介的进程通信方式。在Go语言中，通过os包提供了对命名管道的支持。在本文中，我们将介绍如何在Go中使用命名管道来实现进程间通信。一、命名管道的概念命名管道是一种特殊的文件，可以被多个进程同时访问。在Linux系统中，命名管道是一种特殊的文件类型，它们存在于文件系统的某个位置上，并且可以在

如何在Go中使用第三方库？May 11, 2023 pm 03:30 PM

在Go语言中，使用第三方库是非常方便的。许多优秀的第三方库和框架可以帮助我们快速地开发应用程序，同时也减少了我们自己编写代码的工作量。但是如何正确地使用第三方库，确保其稳定性和可靠性，是我们必须了解的一个问题。本文将从以下几个方面介绍如何使用第三方库，并结合具体例子进行讲解。一、第三方库的获取Go语言中获取第三方库有以下两种方式：1.使用goget命令首先

如何在 Windows 11 中按需使用 OneDrive 的文件Apr 14, 2023 pm 12:34 PM

<p>Windows 系统上的 OneDrive 应用程序允许您将文件存储在高达 5 GB 的云上。OneDrive 应用程序中还有另一个功能，它允许用户选择一个选项，是将文件保留在系统空间上还是在线提供，而不占用您的系统存储空间。此功能称为按需文件。在这篇文章中，我们进一步探索了此功能，并解释了有关如何在 Windows 11 电脑上的 OneDrive 中按需使用文件的各种选项。</p><h2>如何使用 On

如何在Go中使用WebSocket？May 11, 2023 pm 04:17 PM

近年来，WebSocket技术已经成为了Web开发中不可或缺的一部分。WebSocket是一种在单个TCP连接上进行全双工通信的协议，它使得客户端和服务器之间的通信更加流畅和高效。如今，很多现代的Web应用程序都使用了WebSocket技术，例如实时聊天、在线游戏以及实时数据可视化等。Go语言作为一个现代的编程语言，自然也提供了很好的支持WebSock

如何让MySQL外键和主键自动关联起来？Mar 15, 2024 pm 12:54 PM

如何让MySQL外键和主键自动关联起来？在MySQL数据库中，外键和主键是非常重要的概念，它们能够帮助我们在不同表之间建立关联关系，保证数据的完整性和一致性。在实际的应用过程中，经常需要让外键自动关联到对应的主键上，以避免数据不一致的情况发生。下面将介绍如何通过具体的代码示例实现这一功能。首先，我们需要创建两个表，一个表作为主表，另一个表作为从表。在主表中创

如何在Go中使用反转依赖？May 11, 2023 pm 03:39 PM

Go语言中的反转依赖是一种非常实用的技术，它可以帮助开发者更好地进行软件开发。在本文中，我们将详细介绍什么是反转依赖，并且演示如何在Go语言中使用它来优化软件。一、什么是反转依赖在传统的软件开发中，模块之间存在着依赖关系。一些模块被其他模块所依赖，而另一些模块则依赖于其他模块。这种依赖关系在软件中非常普遍，但同时也会带来很多问题。一旦一个模块的代码发生了变化

深入了解HTTP状态码100：它代表什么意思？Feb 20, 2024 pm 04:15 PM

深入了解HTTP状态码100：它代表什么意思？HTTP协议是现代互联网应用中最为常用的协议之一，它定义了浏览器和Web服务器之间进行通信所需的标准规范。在HTTP请求和响应的过程中，服务器会向浏览器返回各种类型的状态码，以反映请求的处理情况。其中，HTTP状态码100是一种特殊的状态码，用来表示"继续"。HTTP状态码由三位数字组成，每个状态码都有特定的含义

深入了解Linux ldconfigMar 14, 2024 pm 03:39 PM

Linuxldconfig是一个用于动态链接库管理的工具，可以帮助系统在运行时找到并加载共享库。它主要用于更新系统的动态链接器运行时连接库缓存，以保证程序可以正确链接到共享库。ldconfig主要用于两个方面：一是添加、删除共享库路径，并更新相关信息到配置文件中；二是根据配置文件中的路径重新生成动态连接库链接器的缓存。接下来将介绍如何使用ldconf

See all articles