0

There is a Postgres database. There is a Foo table with the id primary key. There is a many-to-many table Bar which contains a row which holds a Foreign Key key which references a non-existent foo object. Resulting in:

ERROR:  insert or update on table "Bar" violates foreign key constraint "resu_foo_id_62eaf76ab86870ae_fk_foo_id"
DETAIL:  Key (foo_id)=(123) is not present in table "Foo".

What is the way to get all rows in all tables whose foreign keys reference non-existent objects?

I would like to find those and delete them.

In other words, I want a script which will keep the database integrity by removing all non-existent references to already deleted objects.

For example there is this guide: https://www.cybertec-postgresql.com/en/index-your-foreign-key/. Which seems to find foreign keys without indexes. I want to get the opposite - the indexes that point to non-existing primary keys.

0leg
  • 13,464
  • 16
  • 70
  • 94
  • 1
    Are you sure this is the case? The error you mention shows up when you try to insert a new *invalid* row. But the database has kept its integrity. Please provide an example. – The Impaler Jul 07 '20 at 10:13
  • The error shown pops if one drops all data from the database, then tries to import SQL dump. Unfortunately, the database is quite-complex with dozens of thousand of records (~350mb) and commercially-protected. So I can only try to explain using the foo-bars dummies. – 0leg Jul 07 '20 at 10:39

1 Answers1

4

For starters, columns with a FK constraint on them never reference non-existing values. If that happens, your database is broken and you need to investigate the cause first. Personally, I have never had this kind of breakage.

The error message you show, like @TheImpaler commented, is the FK constraint doing its job of preserving referential integrity and preventing such breakage.

Theoretically, this query finds dead links:

SELECT *
FROM   "Bar" b
WHERE  NOT EXISTS (
   SELECT FROM "Foo" f
   WHERE  f.id = b.foo_id
   );

Or one of the other standard techniques:

But if your DB is broken (corrupted index etc.), the query is just as unreliable as the broken reference. Find and fix the breakage first. Look for "PostgreSQL corruption", maybe start with the Wiki page ...

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • It seems to me that the problem must be somewhere else. – The Impaler Jul 07 '20 at 10:14
  • This is a possible approach. In this case, one would have to manually run the query for each table. I suppose there is no way to have some generic type for all tables/relations in the databases. – 0leg Jul 07 '20 at 10:16
  • 1
    @Oleg: There are always ways to automate it for the whole database: Loop through the system catalog `pg_depend` and run the query dynamically for every FK. Related: https://dba.stackexchange.com/a/102498/3684. **But** first make sure you are not barking up the wrong tree. – Erwin Brandstetter Jul 07 '20 at 10:20
  • Good point into a right direction. I will go with this approach. – 0leg Jul 07 '20 at 10:39
  • 1
    Since the error occurs during "import SQL dump". This looks like the import is attempting to reload the child table **before** the parent thus resulting in the FK violation. We can assume (be careful) valid integrity when the dump was taken. So before restoring drop the FKs then recreate them afterward. – Belayer Jul 07 '20 at 19:37
  • @Belayer: Good lead. Oleg should investigate how the dump is created. `pg_dump` produces a dump that creates FK constraints *after* filling the tables by default. – Erwin Brandstetter Jul 07 '20 at 22:13