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 the 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:
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:
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:
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:
A foreign key must reference columns that either are a primary key or form a unique constraint.Seems like you are on to something ... – Croesus