信息架构中reference _ constraints . unique _ constraint _ *列的值为空

Question

在Postgres 10中，我声明了以下内容：

create table test_abc (
    pk integer not null,
    id integer not NULL,
    id2 integer not null,
    PRIMARY KEY (pk)
);
CREATE UNIQUE INDEX test_abc_ids ON test_abc(id,id2);

然后是第二个表，其中FK引用第一个表：

create table test_def (
    id integer not null,
    abc_id integer,
    abc_id2 integer,
    PRIMARY KEY (id),
    FOREIGN KEY (abc_id,abc_id2) references test_abc(id,id2)
);

现在考虑这个查询的输出:

SELECT unique_constraint_catalog, unique_constraint_schema, unique_constraint_name
FROM   information_schema.referential_constraints r
WHERE  r.constraint_name = 'test_def_abc_id_fkey'
----------------------
NULL NULL NULL

所有＜code＞unique_constraint_*

从 Postgres 文档中可以看出，这些元列应包含

包含外键约束引用的唯一或主键约束的 [对象] 的名称（始终为当前数据库）

问:我肯定在同一个数据库中，并且在< code>test_abc表上声明的惟一索引是一个惟一约束(否则我就不能一开始就声明FK)，那么为什么这些列是空的呢？

我使用带有一些连接的< code > referential _ constraints 来获取关于外键所引用的列的信息，但是这样我就错过了所有设置了索引的唯一约束的列。

Answer 1

您假定约束名称test_def_abc_id_fkey，这是您在 Postgres 11 或更早版本中设置产生的默认名称。但值得注意的是，Postgres 12 的默认名称已得到改进，其中相同的设置会导致test_def_abc_id_abc_id2_fkey。帖子 12 的发行说明：

>

为外键选择默认约束名称时使用所有键列的名称（Peter Eisentraut）

以前，约束名中只包含第一个列名，导致多列外键不明确。

见：



因此，让我们对 FK 约束使用显式名称test_def_abc_fkey以避免混淆：

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

这在后记9.5 - 后记12中有效。
即使在后记9.3中。
（我一直错误地认为需要实际的约束。

您从查询信息模式中观察到：

SELECT *
FROM   information_schema.referential_constraints
WHERE  constraint_name = 'test_def_abc_fkey';  -- unequivocal name

我们得到一行，但是三个字段unique_constraint_catalog、unique_constraint_schema和unique_constraint_name是NULL。

解释似乎很简单。正如手册所说，这些列描述了：

...外键约束引用的唯一键或主键约束

但是没有＜code＞UNIQUEUNIQUE约束是使用Postgres中的UNIQUEindex实现的。约束由SQL标准定义，索引是实现细节。有你发现的差异。相关的：

• PostgreSQL如何强制UniQUE约束/它使用什么类型的索引？

具有实际 UNIQUE 约束的相同测试按预期显示数据：



所以这似乎是有道理的。特别是因为信息模式也是由SQL标准委员会定义的，索引不是标准化的，只有约束。（信息架构视图中没有索引信息。

都清楚了吗？不完全是。

还有另一个信息模式视图key_column_usage。其最后一列描述为：

position_in_unique_constraint…对于外键约束，引用列在其唯一约束内的顺序位置（计数从1开始）；否则为空

大胆强调我的。这里，列在索引中的序号位置无论如何都会列出:

SELECT *
FROM   information_schema.key_column_usage
WHERE  constraint_name = 'test_def_abc_fkey';

见：



似乎不一致。

更糟糕的是，手册声称创建外键约束需要实际的主键或唯一约束：

外键必须引用作为主键或形成唯一约束的列。这意味着被引用的列总是有一个索引（主键或唯一约束的基础）；因此检查引用行是否匹配将是有效的。

似乎是一个文档错误？如果没人能指出我在这里出了什么问题，我会提交一份错误报告。

相关：

< li>Postgres唯一约束与索引

我使用带有一些连接的< code > referential _ constraints 来获取关于外键所引用的列的信息，但是这样我就错过了所有设置了索引的唯一约束的列。

在Postgres中，系统目录是真实的来源。见：

• 信息模式与系统目录

所以你可以使用这样的东西（就像我在上面的小提琴中也添加的那样）：

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

返回：

conname           | fk_table | fk_columns       | ref_table | ref_key_columns
:---------------- | :------- | :--------------- | :-------- | :--------------
test_def_abc_fkey | test_def | {abc_id,abc_id2} | test_abc  | {id,id2}       

相关：

• 使用表、字段和架构名称查找引用的表名
• 查找外键约束的引用字段
• 如何找到通过外键引用特定行的表？