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.