外键
又称外键约束，Foreign key constraint。
外键是一个表中，用于标识另一张表中行的一个字段或多个字段。包含外键的表称为引用表，外键引用表称为被引用表。所谓外键约束是指引用字段必须在被引用字段中出现。被引用字段需要是唯一约束或主键。
外键约束维护引用表和被引用表之间的参照完整性（referential integrity）。
外键约束可以在创建表时定义，也可以在表创建后通过alter table语句定义。
定义外键约束的完整语法
[ CONSTRAINT constraint_name ] FOREIGN KEY ( column_name [, … ] ) REFERENCES reftable [ ( refcolumn [, … ] ) ] [ MATCH matchtype ] [ ON DELETE action ] [ ON UPDATE action ]
其中：
constraint_name : 外键约束名称
( column_name [, … ] ) : 引用表中的引用字段
reftable : 被引用表
( refcolumn [, … ] ) : 被引用表中的被引用字段，和( column_name [, … ] )对应。
MATCH [SIMPLE|FULL] : 外键匹配模式,如果引用字段全部不是NULL,则强匹配,否则根据匹配模式进行弱匹配。
- SIMPLE,默认值，只要引用字段中任一字段为NULL，则不要求与被引用字段强匹配；
- FULL,只有引用字段全部为NULL，才不要求与被引用字段强匹配。
ON DELETE [CASCADE | NO ACTION] : 默认NO ACTION。
- CASCADE,删除被引用表数据时级联删除引用表数据
- NO ACTION,删除被引用表数据时必须先删除引用表数据，否则，如果引用表如果存在数据，直接删除被引用表数据返回失败。
ON UPDATE [CASCADE | NO ACTION] : 默认NO ACTION
- CASCADE,更新被引用表时级联更新引用表数据
- NO ACTION,更新被引用表时必须先删除引用表数据，否则，如果引用表存在数据，直接更新被引用表数据返回失败。
创建表时定义外键约束
创建一张t_currency表
CREATE TABLE t_currency
(
id int,
shortcut char (3),
PRIMARY KEY (id)
);
创建一张t_product表, 其中包含外键约束currency_id引用t_currency的id字段。pg中定义外键约束需要用到REFERENCES关键字。
CREATE TABLE t_product
(
id int,
name text,
currency_id int REFERENCES t_currency (id),
PRIMARY KEY (id)
);
上面提到被引用字段需要时被引用表的主键和唯一约束。如果我们引用了非主键和唯一约束会发生什么？来看一下。 创建一张表t_product1,定义外键约束引用t_currency的shortcut，shortcut既不是主键，也不是唯一约束。
postgres=# CREATE TABLE t_product1
(
id int,
name text,
currency_id int REFERENCES t_currency (shortcut),
PRIMARY KEY (id)
);
ERROR: there is no unique constraint matching given keys for referenced table "t_currency"
可以看到创建失败，提示“there is no unique constraint matching given keys for referenced table "t_currency"”
创建完外键约束之后，t_product和t_currency之间的参照完整性就建立了，也就说我不能在t_product中插入一条curruncy_id非空但没有出现在t_currency的记录。
尝试在t_product表中插入一条记录，其中currency_id等于1,此时t_currency表中并没有id等于1的记录。
postgres=# INSERT INTO t_product VALUES (1, 'PostgreSQL consulting', 1);
ERROR: insert or update on table "t_product" violates foreign key constraint "t_product_currency_id_fkey"
DETAIL: Key (currency_id)=(1) is not present in table "t_currency".
可以看到执行报错：
ERROR: insert or update on table "t_product" violates foreign key constraint "t_product_currency_id_fkey" DETAIL: Key (currency_id)=(1) is not present in table "t_currency".
这个点在实践场景是非常需要注意的，因为引用表和被引用表之间的参照完整性的存在，就已经确立了表记录的插入顺序。如果没有外键，则可以以任意顺序插入任何表，但有了外键，就需要保证正确的插入顺序。
定义多个字段组成的外键
创建一张表t_unique, 它包含唯一约束uk_tbl_unique_a_b(a,b)
create table t_unique(
a int not null,
b int,
c varchar(10) not null default 'catch u',
constraint uk_tbl_unique_a_b unique(a,b)
);
创建一张表t_child，定义外键约束引用t_unique的a,b字段。
CREATE TABLE t_child(
c1 integer PRIMARY KEY,
c2 integer,
c3 integer,
FOREIGN KEY (c2, c3) REFERENCES t_unique (a, b)
);
给已存在的表定义外键
使用 ALTER TABLE 给一个已存在的表定义外键。
示例：
ALTER TABLE t_child
ADD CONSTRAINT fk_c1 FOREIGN KEY (c1) REFERENCES t_parent (p1);
删除外键
示例：
alter table t_child drop constraint fk_c1;
禁用外键
有时候我们想让外键暂时失效，而不是删除它，可以怎么做？
alter table t_product disable trigger all;
disable trigger all 会禁用表上的所有外键，同时也禁用负责验证约束的内部触发器。
postgres=# INSERT INTO t_product VALUES (1, 'PostgreSQL consulting', 1);
ERROR: insert or update on table "t_product" violates foreign key constraint "t_product_currency_id_fkey"
DETAIL: Key (currency_id)=(1) is not present in table "t_currency".
postgres=# alter table t_product disable trigger all;
ALTER TABLE
postgres=# INSERT INTO t_product VALUES (1, 'PostgreSQL consulting', 1);
INSERT 0 1
这一点在做数据迁移时也很重要。数据迁移的时，遇到有外键约束的表，如果不注意表数据的导入顺序将会导致数据加载失败。
怎么重新启用外键盘约束？
alter table t_product enable trigger all;
看下有没有生效:
postgres=# alter table t_product enable trigger all;
ALTER TABLE
postgres=# INSERT INTO t_product VALUES (2, 'PostgreSQL consulting1', 2);
ERROR: insert or update on table "t_product" violates foreign key constraint "t_product_currency_id_fkey"
DETAIL: Key (currency_id)=(2) is not present in table "t_currency".
启用之后，可以发现，外键约束已经启用，它会对于后续新插入或者更新的数据会进行检查。
这里有个问题，怎么去验证老的数据呢？
方法：修改pg_constraint表，将convalidated置为false，然后使用 Alter table validate constraint语句。
示例：
postgres=# select convalidated from pg_constraint where conname = 't_product_currency_id_fkey';
convalidated
--------------
t
(1 row)
postgres=# update pg_constraint set convalidated = false where conname = 't_product_currency_id_fkey';
UPDATE 1
postgres=# select convalidated from pg_constraint where conname = 't_product_currency_id_fkey';
convalidated
--------------
f
(1 row)
postgres=# Alter table t_product validate constraint t_product_currency_id_fkey;
ERROR: insert or update on table "t_product" violates foreign key constraint "t_product_currency_id_fkey"
DETAIL: Key (currency_id)=(1) is not present in table "t_currency".
我们现在知道了对于存在外键约束的表，表数据的插入顺序很重要，对于这一点，cybertec提供了一个魔法SQL，用于查询我们应该插入数据的顺序。
WITH RECURSIVE fkeys AS (
/* source and target tables for all foreign keys */
SELECT conrelid AS source,
confrelid AS target
FROM pg_constraint
WHERE contype = 'f'
),
tables AS (
( /* all tables ... */
SELECT oid AS table_name,
1 AS level,
ARRAY[oid] AS trail,
FALSE AS circular
FROM pg_class
WHERE relkind = 'r'
AND NOT relnamespace::regnamespace::text LIKE ANY
(ARRAY['pg_catalog', 'information_schema', 'pg_temp_%'])
EXCEPT
/* ... except the ones that have a foreign key */
SELECT source,
1,
ARRAY[ source ],
FALSE
FROM fkeys
)
UNION ALL
/* all tables with a foreign key pointing a table in the working set */
SELECT fkeys.source,
tables.level + 1,
tables.trail || fkeys.source,
tables.trail @> ARRAY[fkeys.source]
FROM fkeys
JOIN tables ON tables.table_name = fkeys.target
/*
* Stop when a table appears in the trail the third time.
* This way, we get the table once with "circular = TRUE".
*/
WHERE cardinality(array_positions(tables.trail, fkeys.source)) < 2
),
ordered_tables AS (
/* get the highest level per table */
SELECT DISTINCT ON (table_name)
table_name,
level,
circular
FROM tables
ORDER BY table_name, level DESC
)
SELECT table_name::regclass,
level
FROM ordered_tables
WHERE NOT circular
ORDER BY level, table_name;
输出结果示例：
table_name | level
------------+-------
t_currency | 1
t_product | 2
(2 rows)
该篇已首发到公众号PostgreSQL运维技术，欢迎来踩~
参考文档
www.cybertec-postgresql.com/en/postgres… www.infoq.cn/article/kah…
