Postgresql – Postgres: find all FK constraints associated with a given PK

constraintforeign keypostgresqlprimary-key

Given some input PK column, e.g. ani.animals.id, I want to find all FK constraints names associated with that PK.

I also need table and schema's names where each particular FK constraint is located.

So basically I am looking for all tuples ( schema_name.table_name, FK_constraint_name ) such that FK_constraint_name is referencing input PK.

As an example, the input PK ani.animals.id has:

  • fk_vet_animal in table ani.vets
  • fk_person_animal in table usr.people

Any ideas how to tackle it?

My ultimate goal is to, given some PK, drop all the found FK constraints, and re-create them with ON UPDATE CASCADE.

Best Answer

Does the following query work for you?

SELECT nr.nspname AS table_schema,
        r.relname AS table_name,
        c.conname AS constraint_name,
        split_part ( split_part ( pg_get_constraintdef ( c.oid ), '(', 2 ), ')', 1 ) AS column_names,
        nr2.nspname AS r_table_schema,
        r2.relname AS r_table_name,
        split_part ( split_part ( pg_get_constraintdef ( c.oid ), '(', 3 ), ')', 1 ) AS r_column_names,
        CASE c.confmatchtype
            WHEN 'f' THEN 'MATCH FULL '
            WHEN 'p' THEN 'MATCH PARTIAL '
            WHEN 'u' THEN 'MATCH NONE '
            ELSE ''
            END
            || CASE c.confupdtype
                WHEN 'c' THEN 'ON UPDATE CASCADE '
                WHEN 'n' THEN 'ON UPDATE SET NULL '
                WHEN 'd' THEN 'ON UPDATE SET DEFAULT '
                WHEN 'r' THEN 'ON UPDATE RESTRICT '
                WHEN 'a' THEN 'ON UPDATE NO ACTION '
                ELSE ''
                END
            || CASE c.confdeltype
                WHEN 'c' THEN 'ON DELETE CASCADE'
                WHEN 'n' THEN 'ON DELETE SET NULL'
                WHEN 'd' THEN 'ON DELETE SET DEFAULT'
                WHEN 'r' THEN 'ON DELETE RESTRICT'
                WHEN 'a' THEN 'ON DELETE NO ACTION'
                ELSE ''
                END AS constraint_rule,
        d.description AS comments
    FROM pg_class r
    INNER JOIN pg_namespace nr
        ON ( nr.oid = r.relnamespace )
    INNER JOIN pg_constraint c
        ON ( c.conrelid = r.oid )
    LEFT OUTER JOIN pg_description d
        ON ( d.objoid = c.oid )
    INNER JOIN pg_catalog.pg_class r2
        ON ( r2.oid = c.confrelid )
    INNER JOIN pg_namespace nr2
        ON ( nr2.oid = r2.relnamespace )
    WHERE r.relkind = 'r'
        AND c.contype = 'f'
        --AND nr.nspname = ?
        --AND r.relname = ?
        AND nr2.nspname = ?
        AND r2.relname = ?
Related Question