时间戳

首页 > 解决方案 > 信息架构中的 referential_constraints.unique_constraint_* 列的 NULL 值

sql - 信息架构中的 referential_constraints.unique_constraint_* 列的 NULL 值

问题描述

在 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 文档看来，这些元列应该包含

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

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

我正在使用referential_constraints一些连接来获取有关我的外键引用的列的信息，但是这样我就错过了所有使用索引设置唯一约束的那些。

标签： sqlpostgresqlforeign-keysinformation-schemadatabase-metadata

解决方案

测试设置

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

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

    以前，约束名称中只包含第一列名称，导致多列外键的歧义。

看：

db<>在这里摆弄

因此，让我们为 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)
);

这适用于 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 索引。使用Postgres 中的索引UNIQUE来实现约束。UNIQUE约束由 SQL 标准定义，索引是实现细节。存在与您发现的差异一样的差异。有关的：

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

db<>在这里摆弄

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

全清？不完全的。

然而

还有另一个信息架构视图key_column_usage。它的最后一列被描述为：

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

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

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

看：

db<>在这里摆弄

似乎不一致。

更糟糕的是，手册声称创建约束需要实际PRIMARY KEY或约束：UNIQUEFOREIGN KEY

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

似乎是一个文档错误？如果没有人能指出我在这里出错的地方，我会提交一个错误报告。

有关的：

解决方案

我正在使用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_key_columns
:---------------- | :------- | :--------------- | :-------- | :--------------
test_def_abc_fkey | 测试定义 | {abc_id,abc_id2} | test_abc | {id,id2}

有关的：

推荐阅读