信息模式中referential_constraints.unique_constraint_ *列的NULL值 [英] NULL values for referential_constraints.unique_constraint_* columns in information schema

问题描述

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

In Postgres 10 I have declared the following:

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引用第一个：

And then a second table with a FK referencing the first:

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

现在考虑此查询的输出：

Now consider the output of this query:

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

From the Postgres documentation it seems these meta columns should contain the

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

name of the [object] that contains the unique or primary key constraint that the foreign key constraint references (always the current database)

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

Question: I'm surely in the same database, and the unique index declared on test_abc table is a unique constraint (otherwise I wouldn't be able to declare the FK to begin with), so why are these columns empty?

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

I'm using the referential_constraints with some joins to get information about the columns referenced by my foreign keys, but this way I'm missing all those where the unique constraint is set with an index.

推荐答案

测试设置

您假设约束名称 test_def_abc_id_fkey ，这是您在Postgres 11或更旧版本中的设置所产生的默认名称。不过，值得注意的是，Postgres 12的默认名称已得到改进，在相同的设置下， test_def_abc_id_abc_id2_fkey 的默认名称也得到了改进。 Postgres 12的发行说明：

Test setup

You assume the constraint name test_def_abc_id_fkey, the default name resulting from your setup in Postgres 11 or older. Worth noting, though, that default names have been improved for Postgres 12, where the same setup results in test_def_abc_id_abc_id2_fkey. The release notes for Postgres 12:

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

• Use all key columns' names when selecting default constraint names for foreign keys (Peter Eisentraut)

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

Previously, only the first column name was included in the constraint name, resulting in ambiguity for multi-column foreign keys.

请参见：

db<>小提琴< a href = https://dbfiddle.uk/?rdbms=postgres_12&fiddle=999901d428ee4b21e561ca093​​685b500 rel = nofollow noreferrer>此处

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

So let's use the explicit name test_def_abc_fkey for the FK constraint to avoid confusion:

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-Postgres12。

即使在Postgres 9.3中也是如此。

（我一直有错误的印象，需要实际的约束。）

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

Your observation from querying the information schema holds:

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 。

We get a row, but the three fields unique_constraint_catalog, unique_constraint_schema and unique_constraint_name are NULL.

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

The explanation seems simple. Those columns describe, as the manual puts it:

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

... the unique or primary key constraint that the foreign key constraint references

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

But there is no UNIQUE constraint, just a UNIQUE index. A UNIQUE constraint is implemented using a UNIQUE index in Postgres. Constraints are defined by the SQL standard, indexes are implementation details. There are differences like the one you discovered. Related:

• PostgreSQL如何执行UNIQUE约束/使用哪种类型的索引？

• How does PostgreSQL enforce the UNIQUE constraint / what type of index does it use?

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

The same test with an actual UNIQUE constraint shows data as expected:

db<> fiddle 这里

所以这似乎很有意义。特别是由于信息架构也由SQL标准委员会和索引不是标准化的，只有约束。 （信息架构视图中没有索引信息。）

So this seems to make sense. Especially since the information schema is also defined by the SQL standards committee and indexes are not standardized, only constraints. (No index information in information schema views.)

全部清除吗？不完全是。

All clear? Not quite.

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

There is another information schema view key_column_usage. Its last column is described as:

position_in_unique_constraint ...键约束，被引用列在其唯一约束中的顺序位置（计数从1开始）；否则为null

position_in_unique_constraint ... For a foreign-key constraint, ordinal position of the referenced column within its unique constraint (count starts at 1); otherwise null

Bold 强调我的。在这里，列在 index 中的顺序位置仍然会列出：

Bold emphasis mine. Here, the ordinal position of the column in the index is listed anyway:

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

请参阅：

db< ;> fiddle 此处

似乎不一致。

更糟糕的是，手册声称实际的主键或 UNIQUE 约束是创建 FOREIGN KEY 约束所必需的：

What's worse, the manual claims that an actual PRIMARY KEY or UNIQUE constraint would be required for the creation of a FOREIGN KEY constraint:

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

A foreign key must reference columns that either are a primary key or form a unique constraint. This means that the referenced columns always have an index (the one underlying the primary key or unique constraint); so checks on whether a referencing row has a match will be efficient.

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

Seems to be a documentation bug? If nobody can point out where I am going wrong here, I'll file a bug report.

相关：

• Postgres唯一约束与索引

• Postgres unique constraint vs index

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

I'm using the referential_constraints with some joins to get information about the columns referenced by my foreign keys, but this way I'm missing all those where the unique constraint is set with an index.

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

In Postgres, the system catalog is the actual source of truth. See:

• 信息架构与系统目录

• Information schema vs. system catalogs

因此您可以使用类似的方式（例如我也在小提琴）：

So you could use something like this (like I also added in the 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';

返回值：

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

相关：

• 查找引用的表名使用表，字段和架构名称


• 查找外键约束的引用字段


• 如何找到通过外键引用特定行的表？ a>

这篇关于信息模式中referential_constraints.unique_constraint_ *列的NULL值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！