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

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

粗体强调我的.此处，列在 index 中的序号位置无论如何都会列出:

选择 *FROM information_schema.key_column_usageWHERE constraint_name = 'test_def_abc_fkey';

见:

db<>小提琴这里

似乎不一致.

更糟糕的是，手册声称创建 FOREIGN KEY 约束需要一个实际的 PRIMARY KEY 或 UNIQUE 约束:

外键必须引用为主键或形成唯一约束.这意味着引用的列总是有一个索引(主键或唯一的索引约束);因此检查引用行是否匹配将高效.

似乎是一个文档错误?如果没有人能指出我哪里出错了，我会提交一个错误报告.

相关:

• Postgres 唯一约束与索引

解决方案

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

在 Postgres 中，系统目录是真实的真实来源.见:

• 信息架构与系统目录

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

SELECT c.conname, c.conrelid::regclass AS fk_table, k1.fk_columns, c.confrelid::regclass AS ref_table, k2.ref_key_columnsFROM pg_catalog.pg_constraint c左连接横向 (选择数组(选择一个.attnameFROM pg_catalog.pg_attribute 一个, unnest(c.conkey) WITH ORDINALITY AS k(attnum, ord)其中 a.attrelid = c.conrelidAND a.attnum = k.attnum由 k.ord 订购) 作为 fk_columns) k1 ON 真左连接横向 (选择数组(选择一个.attnameFROM pg_catalog.pg_attribute 一个, unnest(c.confkey) WITH ORDINALITY AS k(attnum, ord)哪里 a.attrelid = c.confrelidAND a.attnum = k.attnum由 k.ord 订购) 作为 ref_key_columns) k2 ON 真WHERE conname = 'test_def_abc_fkey';

返回:

相关:

• 查找引用使用表名、字段名和模式名的表名
• 查找外键约束的引用字段
• 如何找到通过外键引用特定行的表?

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

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

All unique_constraint_* columns have a null value.

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)

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?

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 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:

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

See:

db<>fiddle here

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

And that works in Postgres 9.5 - Postgres 12.
Even in Postgres 9.3.
(I had been under wrong impression an actual constraint would be required.)

Answer

Your observation from querying the information schema holds:

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

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

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:

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

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

db<>fiddle here

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.

However

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

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

See:

db<>fiddle here

Seems inconsistent.

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.

Related:

• Postgres unique constraint vs index

Solution

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.

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

Returns:

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

Related:

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

这篇关于信息架构中的 referential_constraints.unique_constraint_* 列的 NULL 值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，WP2