1

I have a table to which a bunch of other tables has an FK reference. Is there any way of deleting records from this table only if they are not being referenced?

I know that I can left join the referencing table and check for null, but I have about 10 tables (more will be added) with FKs referencing this table, so it would be cool to have a generic way of doing it.

There are often not more than a handful of records I need to remove. I suppose I could loop and try to remove each record individually and protect each deletion with BEGIN/EXCEPT, but that is an ugly concept.

Does this kind of functionality exist in Postgres? Kind of a soft delete, or delete-if-allowed.

1 Answer 1

1

I can think of no better way than doing:

DELETE FROM referenced_table AS r
WHERE NOT EXISTS (SELECT 1 FROM referencing_table_1
                  WHERE ref_id = r.id)
  AND NOT EXISTS (SELECT 1 FROM referencing_table_2
                  WHERE ref_id = r.id)
  AND ...;

This is a very unusual requirement.

If you need to do that often, you could even consider denormalizing your database:

  • add a column reference_count to the referenced table

  • define triggers that update reference_count whenever rows in the referencing tables are added, deleted or modified

Then you can simply delete the rows with reference_count = 0. Processing will be fast (if all your foreign keys are indexed!!), but you buy that at the price of slowing down all data modifications on all referencing tables.

5
  • A semi anti-join is often quicker rather than a subquery...
    – SQLpro
    Sep 14, 2020 at 16:26
  • @SQLpro That will result in a semi-join. Sep 14, 2020 at 16:37
  • Thanks. This is for a nightly cleanup-job to get rid of orphans (orphans can happen rarely if a client only syncs partly due to bad reception and then the user removes the local data before getting reception for a new sync attempt). Performance will not be an issue. I suspected there was no other way than checking manually via a left join or IN clause. Accepting this answer as a "nope, can't be done".
    – Michael
    Sep 15, 2020 at 8:10
  • and.. I have worked hard for my normalized database. Some stuff gets harder with a heavily normalized db, but it's so sweet to have total control. Not to mention how easy it is to build out the db when new requirements arrive. I don't want to denormalize =)
    – Michael
    Sep 15, 2020 at 8:15
  • This answer takes too long and results in a query timeout for me, where referenced_table has 300k rows, and referencing_table_1 has 600k rows
    – Cherona
    May 6, 2022 at 13:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.