Tracking foreign keys throughout a schema

I recently ran into the need with a customer to track the usage of a specific key throughout the schema. Basically, "what are all the tables and columns referencing this key, directly or indirectly". Luckily, with a little bit of catalog query, that's not hard:

WITH RECURSIVE what (tbl) AS (
   VALUES ('public.tt')
),
t (oid, key, constrid) AS (
 SELECT tbl::regclass::oid, conkey, NULL::oid
  FROM what INNER JOIN pg_constraint ON (contype='p' AND conrelid=tbl::regclass)
UNION ALL
 SELECT conrelid, conkey, c.oid
 FROM pg_constraint c
 INNER JOIN t ON (c.confrelid=t.oid AND c.confkey=t.key)
 WHERE contype='f'
)
SELECT nspname, relname, key, ARRAY(
    SELECT attname FROM pg_attribute a WHERE a.attrelid=t.oid AND attnum=ANY(key)
  )
FROM t
INNER JOIN pg_class cl ON cl.oid=t.oid
INNER JOIN pg_namespace n ON n.oid=cl.relnamespace

The output can be similar to:

 nspname | relname | key | array 
---------+---------+-----+-------
 public  | tt      | {1} | {ttt}
 public  | foo1    | {1} | {a}
 public  | foo2    | {3} | {z}

for a single column key (tt being the table with the primary key in, and the foo1 and foo2 tables referencing it directly or through the other one), or:

 nspname | relname |  key  | array 
---------+---------+-------+-------
 public  | m1      | {1,2} | {a,b}
 public  | m2      | {1,2} | {a,b}

for a multi-column foreign key.

In this particular use-case, it was an efficient way to track down key usage where naming standards for using the key had not always been followed. And of course, we also found a couple of cases where the column had the correct name but lacked the actual FOREIGN KEY definition, but that was done by just looking at the column names.


Comments

How would you do it for all the tables in a specific schema?

Posted on Nov 10, 2018 at 13:47 by Michael Vitale.

You should be able to join it to the list of primary keys available without too much trouble. And those keys can be found from the pg_catalog schema directly, or through information_schema.

Posted on Nov 13, 2018 at 19:10 by Magnus.

Conferences

I speak at and organize conferences around Open Source in general and PostgreSQL in particular.

Upcoming

SCALE 17x + PGDay
Mar 7-10, 2019
Pasadena, California, USA
pgDay.Paris 2019
Mar 12, 2019
Paris, France
Nordic PGDay 2019
Mar 19, 2019
Copenhagen, Denmark
PGConf.DE 2019
May 10, 2019
Leipzig, Germany
PGDay.IT 2019
May 17, 2019
Bologna, Italy
PGCon 2019
May 27-31, 2019
Ottawa, Canada

Past

FOSDEM+PGDay 2019
Feb 1-3, 2019
Brussels, Belgium
PGConf.Asia 2018
Dec 10-12, 2018
Tokyo, Japan
DC PostgreSQL Users Group
Nov 14, 2018
Washington DC, USA
New York City PostgreSQL User Group
Nov 13, 2018
New York City, NY, USA
Driving IT 2018
Nov 2, 2018
Copenhagen, Denmark
More past conferences