信息模式中referential_constraints.unique_constraint_ *列的NULL值

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

所有unique_constraint_*列都有一个空值。

从Postgres文档看来，这些meta列应包含

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

问题： 我肯定在同一个数据库中，并且在test_abc表上声明的唯一索引是唯一的约束（否则我将无法声明FK开头），那么为什么这些列为空？

我使用referential_constraints带有一些联接的来获取有关我的外键引用的列的信息，但是这样我会丢失所有使用索引设置唯一约束的列。

Answer 1

测试设置

您假定约束名称test_def_abc_id_fkey，这是您在Postgres
11或更早版本中的设置所产生的默认名称。值得注意的是，虽然，缺省名称已被Postgres的12，其中相同的设置导致改善test_def_abc_id_abc_id2_fkey。Postgres
12的发行说明：

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

以前，约束名称中仅包含第一列名称，从而导致多列外键不明确。

看：

db
<>在这里拨弄

因此，让我们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中也可以。
（我一直有错误的印象，将需要实际的 约束 。）

回答

您从查询信息模式中得到的观察结果是：

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。

解释似乎很简单。如手册所述，这些专栏描述了：

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

但是没有UNIQUE 约束 ，只有UNIQUE
索引
。一个UNIQUE约束是使用实现UNIQUE了在Postgres指数。约束由SQL标准定义，索引是实现细节。存在与您发现的差异之间的差异。有关的：

• PostgreSQL如何执行UNIQUE约束/它使用什么类型的索引？

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

db
<>在这里拨弄

因此，这似乎是有道理的。特别是由于信息模式也是由SQL标准委员会定义的，并且索引不是标准化的，因此仅是约束条件。（信息架构视图中没有索引信息。）

全清？不完全的。

然而

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

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

大胆 强调我的。这里，无论如何，列在 索引中 的顺序位置：

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

看：

db
<>在这里拨弄

似乎不一致。

更糟糕的是，该手册声称创建约束将需要实际约束PRIMARY KEY或约束：UNIQUE``FOREIGN KEY

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

似乎是 文档错误 ？如果没有人指出我在哪里出了问题，我将提交一个错误报告。

有关的：

• Postgres唯一约束与索引

解决方案

我使用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';

返回值：

名| fk_table | fk_columns | ref_table | ref_key_columns
：---------------- | ：------- | ：--------------- | ：-------- | ：--------------
test_def_abc_fkey | test_def | {abc_id，abc_id2} | test_abc | {id，id2}

有关的：

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