Map FKs in Postgres

postgres_map_fks.md

Table constraints can include multiple columns. The trick to getting this right is to join each column by their constraint ordinal positions. If you don't join correctly your script will blow up with duplicate rows 😥 whenever a table has multiple columns in a unique constraint.

Table Notes

This may be helpful in understanding what these tables do.

information_schema.table_constraints

• lists all table constraints
• explains constraint types
• does not include column information

information_schema.referential_constraints

• maps fk constraints to pk constraints.
  • constraint_name = fk constraint name
  • unique_constraint_name = pk constraint name
• does not include column information

information_schema.key_column_usage

• list column level constraint info
• includes column ordinal positions in constraints. this is important!

Query

Lists all foreign key columns and their references.

select
       -- unique reference info
       ref.table_catalog    as ref_database,
       ref.table_schema     as ref_schema,
       ref.table_name       as ref_table,
       ref.column_name      as ref_column,
       refd.constraint_type as ref_type, -- e.g. UNIQUE or PRIMARY KEY

       -- foreign key info
       fk.table_catalog as fk_database,
       fk.table_schema  as fk_schema,
       fk.table_name    as fk_table,
       fk.column_name   as fk_column,
       map.update_rule  as fk_on_update,
       map.delete_rule  as fk_on_delete

-- lists fk constraints and maps them to pk constraints
from information_schema.referential_constraints as map

-- join unique constraints (e.g. PKs constraints) to ref columns info
inner join information_schema.key_column_usage as ref
    on  ref.constraint_catalog = map.unique_constraint_catalog
    and ref.constraint_schema = map.unique_constraint_schema
    and ref.constraint_name = map.unique_constraint_name

-- optional: to include reference constraint type
left join information_schema.table_constraints as refd
    on  refd.constraint_catalog = ref.constraint_catalog
    and refd.constraint_schema = ref.constraint_schema
    and refd.constraint_name = ref.constraint_name

-- join fk columns to the correct ref columns using ordinal positions
inner join information_schema.key_column_usage as fk
    on  fk.constraint_catalog = map.constraint_catalog
    and fk.constraint_schema = map.constraint_schema
    and fk.constraint_name = map.constraint_name
    and fk.position_in_unique_constraint = ref.ordinal_position --IMPORTANT!

Example

consider the relationship between these to tables.

create table foo (
    a int,
    b int,
    primary key (a,b)
);

create table bar (
    c int,
    d int,
    foreign key (c,d) references foo (b,a) -- i flipped a,b to make a point later.
);

If we check the information_schema.table_constraints table we can see the names of the pk constraint and the fk constraint.

select * from information_schema.table_constraints where table_name in ('foo','bar');

constraint_name	table_name	constraint_type
foo_pkey	foo	PRIMARY KEY
bar_c_d_fkey	bar	FOREIGN KEY

And when we check the information_schema.referential_constraints table we can see that our foreign key constraint depends on our primary keys unique constraint.

select * from information_schema.referential_constraints where constraint_name in ('bar_c_d_fkey');

constraint_name	unique_constraint_name
bar_c_d_fkey	foo_pkey

And finally, we check that information_schema.key_column_usage table. we can see the the position_in_unique_constraint of the FK columns correctly map to the ordinal_position of the PK columns.

Notice that d correctly maps to a and c correctly maps to b per the table definitions above.

select * from information_schema.key_column_usage where table_name in ('foo','bar');

constraint_name	table_name	column_name	ordinal_position	position_in_unique_constraint
foo_pkey	foo	a	1	null
foo_pkey	foo	b	2	null
bar_c_d_fkey	bar	c	1	2
bar_c_d_fkey	bar	d	2	1

Now all that's left is to join them together. The main query above is one way you could do so. here is a much more comprehensive version.

select
    concat('/',c.table_catalog,'/',c.table_schema,'/',c.table_name,'/',c.column_name) as "Path",
    c.table_catalog as "Database",
    c.table_schema as "Schema",
    c.table_name as "Table",
    c.column_name as "Name",
	c.ordinal_position as "Position",
    c.data_type as "Type",
	column_default as "Default",
    CASE c.is_nullable
		WHEN 'YES' THEN cast(1 as boolean)
		ELSE cast(0 as boolean)
	END AS "Nullable",

    CASE c.is_updatable
		WHEN 'YES' THEN cast(1 as boolean)
		ELSE cast(0 as boolean)
	END AS "Updatable",

    c.character_maximum_length as "Length",
    coalesce(c.numeric_precision,c.datetime_precision,c.interval_precision) as "Precision",
    c.numeric_scale as "Scale",

    case
        when uq.constraint_name is not null then true
        when pk.constraint_name is not null then true
        else false
    end as "IsUnique",
    case when pk.constraint_name is not null then true else false end as "IsPrimaryKey",
    pk.ordinal_position as "PrimaryKeyPosition",
    case when fk.constraint_name is not null then true else false end as "IsForeignKey",
    fk.ordinal_position as "ForeignKeyPosition",
    case
        when fk.constraint_name is not null then concat('/',rf.table_catalog,'/',rf.table_schema,'/',rf.table_name,'/',rf.column_name)
        else null
    end as "References",
    rf.update_rule as "OnUpdate",
    rf.delete_rule as "OnDelete"

from information_schema.columns as c

-- primary key constraints
left join (
    select
        u.table_catalog,
        u.table_schema,
        u.table_name,
        u.column_name,

        u.constraint_catalog,
        u.constraint_schema,
        u.constraint_name,
        u.ordinal_position

    from information_schema.key_column_usage as u
    inner join information_schema.table_constraints as r
        on  r.table_catalog = u.table_catalog
        and r.table_schema = u.table_schema
        and r.table_name = u.table_name
        and r.constraint_catalog = u.constraint_catalog
        and r.constraint_schema = u.constraint_schema
        and r.constraint_name = u.constraint_name
    where
        r.constraint_type = 'PRIMARY KEY'
) as pk
    on  pk.table_catalog = c.table_catalog
    and pk.table_schema = c.table_schema
    and pk.table_name = c.table_name
    and pk.column_name = c.column_name

-- unique constraints
left join (
    select
        u.table_catalog,
        u.table_schema,
        u.table_name,
        u.column_name,

        u.constraint_catalog,
        u.constraint_schema,
        u.constraint_name,
        u.ordinal_position

    from information_schema.key_column_usage as u
    inner join information_schema.table_constraints as r
        on  r.table_catalog = u.table_catalog
        and r.table_schema = u.table_schema
        and r.table_name = u.table_name
        and r.constraint_catalog = u.constraint_catalog
        and r.constraint_schema = u.constraint_schema
        and r.constraint_name = u.constraint_name
    where
        r.constraint_type = 'UNIQUE'
) as uq
    on  uq.table_catalog = c.table_catalog
    and uq.table_schema = c.table_schema
    and uq.table_name = c.table_name
    and uq.column_name = c.column_name

-- foreign key constraints
left join (
    select
        u.table_catalog,
        u.table_schema,
        u.table_name,
        u.column_name,

        u.constraint_catalog,
        u.constraint_schema,
        u.constraint_name,
        u.ordinal_position

    from information_schema.key_column_usage as u
    inner join information_schema.table_constraints as r
        on  r.table_catalog = u.table_catalog
        and r.table_schema = u.table_schema
        and r.table_name = u.table_name
        and r.constraint_catalog = u.constraint_catalog
        and r.constraint_schema = u.constraint_schema
        and r.constraint_name = u.constraint_name
    where
        r.constraint_type = 'FOREIGN KEY'
) as fk
    on  fk.table_catalog = c.table_catalog
    and fk.table_schema = c.table_schema
    and fk.table_name = c.table_name
    and fk.column_name = c.column_name

-- all references
left join (
    select
        -- unique reference info
        r.table_catalog    as table_catalog,
        r.table_schema     as table_schema,
        r.table_name       as table_name,
        r.column_name      as column_name,

        -- foreign key info
        f.table_catalog as fk_table_catalog,
        f.table_schema  as fk_table_schema,
        f.table_name    as fk_table_name,
        f.column_name   as fk_column_name,
        m.update_rule  as update_rule,
        m.delete_rule  as delete_rule

    from information_schema.referential_constraints as m

    inner join information_schema.key_column_usage as r
        on  r.constraint_catalog = m.unique_constraint_catalog
        and r.constraint_schema = m.unique_constraint_schema
        and r.constraint_name = m.unique_constraint_name

    inner join information_schema.key_column_usage as f
        on  f.constraint_catalog = m.constraint_catalog
        and f.constraint_schema = m.constraint_schema
        and f.constraint_name = m.constraint_name
        and f.position_in_unique_constraint = r.ordinal_position --IMPORTANT!

) as rf
    on  rf.fk_table_catalog = fk.table_catalog
    and rf.fk_table_schema = fk.table_schema
    and rf.fk_table_name = fk.table_name
    and rf.fk_column_name = fk.column_name
;