Find and fix foreign key violations

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

Code Sample

WITHqAS(SELECTconrelid::regclassASfk_table,confrelid::regclassASpk_table,format('(%s)',(selectstring_agg(format('fk.%I',attname),', ')FROMpg_attributeaJOINunnest(conkey)ia(nr)ONia.nr=a.attnumWHEREattrelid=conrelid))ASfk_fields,format('(%s)',(selectstring_agg(format('pk.%I',attname),', ')FROMpg_attributeaJOINunnest(confkey)ia(nr)ONia.nr=a.attnumWHEREattrelid=confrelid))ASpk_fields,pg_get_constraintdef(oid)FROMpg_constraintWHEREcontype='f')SELECTformat($sql$DO$$BEGINRAISENOTICE'checking Foreign Key %3$s%1$s ==> %4$s%2$s';END;$$;SELECT%1$s,%2$sFROM%3$sASfkLEFTJOIN%4$sASpkON%1$s=%2$sWHERE%2$sISNULLAND%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 nooutput, 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 thisexample, a column namedid. The second column is the referencing column forthe foreign key. This means there is a row,pk_id=4, for which a parentprimary key doesn't exist. You can decide if these keys are valid and if theyare not, you can delete them.

Except as otherwise noted, the content of this page is licensed under theCreative Commons Attribution 4.0 License, and code samples are licensed under theApache 2.0 License. For details, see theGoogle Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2025-07-14 UTC.