To check for a foreign key where the corresponding primary key is missing, run the following command:
Code Sample
WITH q AS ( SELECT conrelid :: regclass AS fk_table , confrelid :: regclass AS pk_table , format ( '(%s)' ,( select string_agg ( format ( 'fk.%I' , attname ), ', ' ) FROM pg_attribute a JOIN unnest ( conkey ) ia ( nr ) ON ia . nr = a . attnum WHERE attrelid = conrelid )) AS fk_fields , format ( '(%s)' ,( select string_agg ( format ( 'pk.%I' , attname ), ', ' ) FROM pg_attribute a JOIN unnest ( confkey ) ia ( nr ) ON ia . nr = a . attnum WHERE attrelid = confrelid )) AS pk_fields , pg_get_constraintdef ( oid ) FROM pg_constraint WHERE contype = 'f' ) SELECT format ( $ sql $ DO $$ BEGIN RAISE NOTICE 'checking Foreign Key %3$s%1$s ==> %4$s%2$s' ; END ; $$ ; SELECT % 1 $ s , % 2 $ s FROM % 3 $ s AS fk LEFT JOIN % 4 $ s AS pk ON % 1 $ s = % 2 $ s WHERE % 2 $ s IS NULL AND % 1 $ s IS NOT NULL /* any NULL on FK side bypasses FK constraint by design */ /* use limit for testing, or detecting that "there is a problem in this table */ -- LIMIT 10 $ sql $ , fk_fields , pk_fields , fk_table , pk_table ) FROM q \ gexec
The output of the script will be similar to the following. If there is no output, there are no violations and you have successfully rebuilt your index.
Output
id | pk_id ----+------- | 4 ( 1 row )
In the above output, the first column shows the primary key columns, in this
example, a column named id
. The second column is the referencing column for
the foreign key. This means there is a row, pk_id=4
, for which a parent
primary key doesn't exist. You can decide if these
keys are valid and if they are not, you can delete them.
