Does anyone know a good solution to determining whether a row is
referenced by a foreign key? The problem is that multiple tables may have
foreign keys referencing a single table; therefore, even if you delete a
given foreign key from one table, the delete on the table with the primary
key may fail, which will cause the transaction to abort. For example, a
table of contacts might be referenced by foreign keys in an account table,
a log table, a group table, and an employee table. If we delete an
account, we want to try to delete the contact, and if it fails we can go
on our merry way because it should just mean that it is referenced by
another table. Querying every single table that could possibly have a
foreign key referencing the contact seems error prone and a duplication of
the foreign key checks.
I don't want to just have a separate transaction for each delete from the
table with the primary key, since that will require placing all the
deletes after the transaction which contains all the other statements.
This would make it unbelievably difficult to use functions in our code to
handle related sql queries, since all the deletes would have to be
postponed till after the rest of the transaction has finished.
-Edwin Grubbs
