How to delete all records which are not referenced from other tables 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.
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.