I have a database schema where if row is deleted from one table the rows it references in another table should also be deleted, unless still referenced by something else.
Eg. Table A has foreign key to table B. When I delete a row from A I also want to delete the referenced row in B, unless it's still referenced by something else (which may be another row in A or in a completely different table C).
The way I currently do this is to have an AFTER DELETE FOR EACH ROW trigger on A, which attempts to delete the row in B, but catches and ignores a foreign_key_violation exception. This works (the foreign keys don't have ON DELETE CASCADE), but it's slow when deleting many rows.
A single query that deletes all the referenced rows in B, if they're not referenced by A or C, is much faster. The problem with that approach is it's error-prone to write and maintain. There may be many other tables that reference B and if they ever change this query has to be updated, which is a maintenance nightmare. I could try to auto-generate the SQL for it by finding foreign key constraints referencing B in information_schema, but that's not a trivial exercise. It also falls short in a more complicated scenario where I want to delete rows in multiple tables (A1, A2, ...) that may all reference B.
Is there an easier way to do this? Postgres obviously knows about all the foreign keys, so is there any way to get it to do the checking for me? I mean a way that works in bulk, not one row at a time.
