测试设置
您假设约束名称test_def_abc_id_fkey
,您在 Postgres 11 或更早版本中的设置产生的默认名称。不过,值得注意的是,Postgres 12 的默认名称已得到改进,相同的设置会导致test_def_abc_id_abc_id2_fkey
. Postgres 12 的发行说明:
- 为外键选择默认约束名称时使用所有键列的名称 (Peter Eisentraut)
以前,约束名称中仅包含第一个列名称,导致多列外键不明确。
See:
数据库小提琴here
所以让我们使用显式名称test_def_abc_fkey
对于 FK 约束以避免混淆:
CREATE TABLE test_abc (
pk int PRIMARY KEY
, id int NOT NULL
, id2 int NOT NULL
);
CREATE UNIQUE INDEX test_abc_ids ON test_abc(id,id2);
CREATE TABLE test_def (
id int PRIMARY KEY
, abc_id int
, abc_id2 int
, CONSTRAINT test_def_abc_fkey -- !
FOREIGN KEY (abc_id,abc_id2) REFERENCES test_abc(id,id2)
);
然后适用于 Postgres 9.5 - Postgres 12, 即使在 Postgres 9.3 中也是如此。
(我一直有一种错误的印象约束将是必需的。)
Answer
您通过查询信息模式观察到的结果是:
SELECT *
FROM information_schema.referential_constraints
WHERE constraint_name = 'test_def_abc_fkey'; -- unequivocal name
我们得到一行,但是三个字段unique_constraint_catalog
, unique_constraint_schema
and unique_constraint_name
are NULL
.
解释似乎很简单。正如手册所述,这些列描述了:
...外键约束引用的唯一或主键约束
但没有UNIQUE 约束,只是一个UNIQUE index. A UNIQUE
约束是使用实现的UNIQUE
Postgres 中的索引。约束由 SQL 标准定义,索引是实现细节。存在一些差异,就像您发现的那样。有关的:
- PostgreSQL 如何强制执行 UNIQUE 约束/它使用什么类型的索引?
与实际测试相同UNIQUE
约束显示预期数据:
数据库小提琴here
所以这似乎是有道理的。尤其是自从信息图式也是由SQL标准委员会定义的,索引没有标准化,只有约束。 (信息架构视图中没有索引信息。)
全清?不完全的。
However
还有另一个信息模式视图key_column_usage。其最后一栏描述为:
position_in_unique_constraint
...对于外键约束,引用列在其唯一的内部的顺序位置约束(计数从 1 开始);否则为空
Bold强调我的。这里,列在index无论如何都列出了:
SELECT *
FROM information_schema.key_column_usage
WHERE constraint_name = 'test_def_abc_fkey';
See:
数据库小提琴here
看起来不一致。
更糟糕的是,手册声称实际的PRIMARY KEY
or UNIQUE
创建一个约束将需要FOREIGN KEY
约束:
外键必须引用主键或
形成唯一约束。这意味着引用的列
总是有一个索引(主键或唯一索引的基础)
约束);因此检查引用行是否有匹配项
要有效率。
似乎是一个文档错误?如果没有人能指出我哪里出错了,我将提交错误报告。
Related:
Solution
我正在使用referential_constraints
通过一些连接来获取有关外键引用的列的信息,但这样我就错过了使用索引设置唯一约束的所有那些信息。
在 Postgres 中,系统目录是实际的事实来源。看:
所以你可以使用这样的东西(就像我也在fiddle above):
SELECT c.conname
, c.conrelid::regclass AS fk_table, k1.fk_columns
, c.confrelid::regclass AS ref_table, k2.ref_key_columns
FROM pg_catalog.pg_constraint c
LEFT JOIN LATERAL (
SELECT ARRAY (
SELECT a.attname
FROM pg_catalog.pg_attribute a
, unnest(c.conkey) WITH ORDINALITY AS k(attnum, ord)
WHERE a.attrelid = c.conrelid
AND a.attnum = k.attnum
ORDER BY k.ord
) AS fk_columns
) k1 ON true
LEFT JOIN LATERAL (
SELECT ARRAY (
SELECT a.attname
FROM pg_catalog.pg_attribute a
, unnest(c.confkey) WITH ORDINALITY AS k(attnum, ord)
WHERE a.attrelid = c.confrelid
AND a.attnum = k.attnum
ORDER BY k.ord
) AS ref_key_columns
) k2 ON true
WHERE conname = 'test_def_abc_fkey';
Returns:
conname | fk_table | fk_columns | ref_table | ref_key_columns
:---------------- | :------- | :--------------- | :-------- | :--------------
test_def_abc_fkey | test_def | {abc_id,abc_id2} | test_abc | {id,id2}
Related:
- 使用表、字段和模式名称查找引用的表名称
- 查找外键约束的引用字段
- 如何找到通过外键引用特定行的表?