Postgres的外键深入使用-Mysql Tutorial-php.cn

Home

Database

Mysql Tutorial

Postgres的外键深入使用

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 07, 2016 pm 02:59 PM

useforeign keygo deep

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

Statement

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact 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状态码由三位数字组成，每个状态码都有特定的含义

如何在Go中使用国际化？May 10, 2023 pm 01:55 PM

随着全球化的发展，越来越多的应用程序需要支持多语言，以便吸引更多的用户使用。在Go语言中如何使用国际化呢？本篇文章将介绍Go中如何使用标准库和第三方库来实现国际化。一、Go标准库实现国际化Go标准库提供了一些方法来实现国际化，其中包括：fmt.Sprintffmt.Sprintf可以使用格式化模板来生成字符串，支持多语言格式字符串。在多语言环境下，你可以使用

See all articles