Sunday, January 27, 2019

Retrieving Foreign Key Columns from PostgreSQL's Information Schema

There seems to be no readily available resource on the internet that correctly describes how to use Postgres' information_schema to retrieve a table of corresponding column names from the two tables making up a foreign key relationship. Therefore, code to produce such a table is shown below.

In the output, the columns named with a "uq" prefix refer to the table with a unique (e.g., primary) key.  The schema, table, and column name columns without the prefix refer to the table containing the primary key values that must exist in the 'unique' table.

Note that the constraint name must be included because Postgres allows more than one foreign key between the same two tables, and these are distinguished by the constraint name.

    cu_uq.table_schema as uq_schema,
    cu_uq.table_name as uq_table,
    cu_uq.column_name as uq_column
    (select distinct constraint_catalog, constraint_schema, constraint_name,
        unique_constraint_catalog, unique_constraint_schema, unique_constraint_name
        from information_schema.referential_constraints) as rc
    inner join (select * from information_schema.table_constraints
            where constraint_type = 'FOREIGN KEY') as tc
        on tc.constraint_catalog = rc.constraint_catalog
        and tc.constraint_schema = rc.constraint_schema
        and tc.constraint_name = rc.constraint_name
    inner join (select * from information_schema.table_constraints
            where constraint_type not in ('FOREIGN KEY', 'CHECK') ) as tc_uq
        on tc_uq.constraint_catalog = rc.unique_constraint_catalog
        and tc_uq.constraint_schema = rc.unique_constraint_schema
        and tc_uq.constraint_name = rc.unique_constraint_name
    inner join information_schema.key_column_usage as cu
        on cu.constraint_catalog = tc.constraint_catalog
        and cu.constraint_schema = tc.constraint_schema
        and cu.constraint_name = tc.constraint_name
        and cu.table_catalog = tc.table_catalog
        and cu.table_schema = tc.table_schema
        and cu.table_name = tc.table_name
    inner join information_schema.key_column_usage as cu_uq
        on cu_uq.constraint_catalog = tc_uq.constraint_catalog
        and cu_uq.constraint_schema = tc_uq.constraint_schema
        and cu_uq.constraint_name = tc_uq.constraint_name
        and cu_uq.table_catalog = tc_uq.table_catalog
        and cu_uq.table_schema = tc_uq.table_schema
        and cu_uq.ordinal_position = cu.ordinal_position

No comments:

Post a Comment