sql - information_schema 中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 documentation似乎这些元列应该包含

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

问题:
我肯定在同一个数据库中，并且在 test_abc 上声明了唯一索引table 是一个唯一的约束(否则我将无法声明 FK 开始)，那么为什么这些列是空的？

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

Answer 1

测试设置

您假设约束名称 test_def_abc_id_fkey ，您在 Postgres 11 或更早版本中设置的默认名称。但值得注意的是，Postgres 12 的默认名称已得到改进，相同的设置导致 test_def_abc_id_abc_id2_fkey . The release notes for Postgres 12:

• 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.

看:

分贝<> fiddle 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)
);

还有那个 works in Postgres 9.5 - Postgres 12.
Even in 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 .

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

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

但是没有 UNIQUE constraint ，只是一个 UNIQUE index .一个 UNIQUE约束是使用 UNIQUE 实现的Postgres 中的索引。约束由 SQL 标准定义，索引是实现细节。有一些差异，就像你发现的那样。有关的:

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

与实际 UNIQUE 相同的测试约束按预期显示数据:

分贝<> fiddle here

所以这似乎是有道理的。特别是自从 information schema也是由 SQL 标准委员会定义的，索引不是标准化的，只有约束。 (信息架构 View 中没有索引信息。)

全清？不完全的。

然而

还有另一个信息架构 View key_column_usage .它的最后一列描述为:

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

加粗 强调我的。在这里，列在索引中的顺序位置无论如何都列出了:

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

看:

分贝<> fiddle here

似乎不一致。

更糟的是， the manual声称实际 PRIMARY KEY或 UNIQUE创建 FOREIGN KEY 需要约束约束:

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.

好像是 文档错误 ?如果没有人能指出我哪里出错了，我将提交错误报告。

有关的:

Postgres unique constraint vs index

解决方案

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 中，系统目录是真实的真实来源。看:

Information schema vs. system catalogs

所以你可以使用这样的东西(就像我在上面的 fiddle 中添加的一样):

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}

有关的:

Find the referenced table name using table, field and schema name

Find referenced field(s) of foreign key constraint

How can I find tables which reference a particular row via a foreign key?