Re: Delete all records NOT referenced by Foreign Keys

On Sun, 14 Dec 2003, D. Dante Lorenso wrote:

> Stephan Szabo wrote:
>
> My hope was that there was some sort of (semaphore? / counter?) associated
> with each row that indicated whether a dependency existed at all.  Although
> that would most likely not be an indexed column, I could apply additional
> WHERE constraints to avoid a full table scan.
>
> If such a counter existed, it would certainly speed up deletes when no
> foreign key checks were necessary.  But I suppose it would also slow
> down deletes when it becomes necessary to decrement a counter for the
> dependencies created by the row being deleted.

More importantly, unless you can defer the counter updates, that means
taking out a write lock on the pk row in even more situations than now
which makes things even more deadlock prone. If you do defer the updates
in some way that removes that likelihood, you need a way to store the
local changes to the counters.

> So...internally, how does a row KNOW that it can not be deleted because of
> a foreign key constraint?  Whatever that mechanism is, isn't there a way for
> me to make use of that as I try my query?

It's a bunch of triggers each doing their own statements in C.  The row
has no knowledge of it, only the triggers do.