On Fri, 8 Feb 2002, Edwin Grubbs wrote:
> 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.
Well, this sounds like something that nested transactions or savepoints
might be able to help with, but neither of those are implemented
currently. ISTM, you're kind of trying to do something foreign keys
really weren't meant for.
You might be able to make a trigger that does reference counting. You'd
have to pay attention to concurrent transaction issues and locking, but
that's managable (at least to the level the fk constraints do which is
not quite good.)
