Stay organized with collectionsSave and categorize content based on your preferences.
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_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 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 namedid. 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.
[[["Easy to understand","easyToUnderstand","thumb-up"],["Solved my problem","solvedMyProblem","thumb-up"],["Other","otherUp","thumb-up"]],[["Hard to understand","hardToUnderstand","thumb-down"],["Incorrect information or sample code","incorrectInformationOrSampleCode","thumb-down"],["Missing the information/samples I need","missingTheInformationSamplesINeed","thumb-down"],["Other","otherDown","thumb-down"]],["Last updated 2025-08-28 UTC."],[],[],null,["# Find and fix foreign key violations\n\nTo check for a foreign key where the corresponding primary key is missing, run\nthe following command: \n\n### Code Sample\n\n```sql\n WITH q AS (\n SELECT conrelid::regclass AS fk_table, \n confrelid::regclass AS pk_table, \n format('(%s)',(select string_agg(format('fk.%I', attname), ', ') \n FROM pg_attribute a \n JOIN unnest(conkey) ia(nr) ON ia.nr = a.attnum\n WHERE attrelid = conrelid)) AS fk_fields, \n format('(%s)',(select string_agg(format('pk.%I', attname), ', ') \n FROM pg_attribute a \n JOIN unnest(confkey) ia(nr) ON ia.nr = a.attnum\n WHERE attrelid = confrelid)) AS pk_fields, \n pg_get_constraintdef(oid)\n FROM pg_constraint\n WHERE contype='f'\n )\n SELECT format(\n $sql$\n DO $$ BEGIN RAISE NOTICE 'checking Foreign Key %3$s%1$s ==\u003e %4$s%2$s'; END;$$;\n SELECT %1$s, %2$s \n FROM %3$s AS fk\n LEFT JOIN %4$s AS pk ON %1$s = %2$s \n WHERE %2$s IS NULL\n AND %1$s IS NOT NULL /* any NULL on FK side bypasses FK constraint by design */\n /* use limit for testing, or detecting that \"there is a problem in this table */\n -- LIMIT 10\n $sql$, fk_fields, pk_fields, fk_table, pk_table\n )\n FROM q\n \\gexec\n \n```\n\nThe output of the script will be similar to the following. If there is no\noutput, there are no violations and you have successfully rebuilt your index. \n\n### Output\n\n```bash\n id | pk_id \n ----+-------\n | 4\n (1 row)\n \n```\n\nIn the above output, the first column shows the primary key columns, in this\nexample, a column named `id`. The second column is the referencing column for\nthe foreign key. This means there is a row, `pk_id=4`, for which a parent\nprimary key doesn't exist. You can decide if these keys are valid and if they\nare not, you can delete them."]]
