sqlpostgresqlforeign-keysinformation-schemadatabase-metadata

NULL values for referential_constraints.unique_constraint_* columns in information schema


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.


Solution

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