Find and fix foreign key violations

To check for a foreign key where the corresponding primary key is missing, run the following command:

Code Sample

WITHqAS(
SELECTconrelid::regclassASfk_table,
confrelid::regclassASpk_table,
format('(%s)',(selectstring_agg(format('fk.%I',attname),', ')
FROMpg_attributea
JOINunnest(conkey)ia(nr)ONia.nr=a.attnum
WHEREattrelid=conrelid))ASfk_fields,
format('(%s)',(selectstring_agg(format('pk.%I',attname),', ')
FROMpg_attributea
JOINunnest(confkey)ia(nr)ONia.nr=a.attnum
WHEREattrelid=confrelid))ASpk_fields,
pg_get_constraintdef(oid)
FROMpg_constraint
WHEREcontype='f'
)
SELECTformat(
$sql$
DO$$BEGINRAISENOTICE'checking Foreign Key %3$s%1$s ==> %4$s%2$s';END;$$;
SELECT%1$s,%2$s
FROM%3$sASfk
LEFTJOIN%4$sASpkON%1$s=%2$s
WHERE%2$sISNULL
AND%1$sISNOTNULL/* 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
)
FROMq
\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
(1row)

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.

Find and fix foreign key violations Stay organized with collections Save and categorize content based on your preferences.

Code Sample

Output

Find and fix foreign key violations