On 10/8/07, chetana bhargav <[EMAIL PROTECTED]> wrote:
> >> Is this a garbage collection situation, where you want a row in B to go
> >> away when all referring rows in A are deleted?
>
> Yes exactly this is what I wanted :)
Okay. A trigger on table A can accomplish it without any extra tracking:
CREATE TRIGGER CleanUpOrphans AFTER DELETE ON A
WHEN NOT EXISTS (SELECT 1 FROM A WHERE refkey = OLD.refkey)
BEGIN
DELETE FROM B WHERE key = OLD.refkey;
END;
If table A has an index on the refkey column, it should be reasonably efficient.
Maintaining a reference count in table B may be more efficient from an
I/O standpoint, but you'll probably have to do checking within app
code (instead of using SQL triggers directly), and it would likely
only pay off if table A has both a vey large number of total rows, as
well as many references to the same key in table B. It's also not as
clean from an SQL standpoint, since it's an additional item that must
be tracked, yet has nothing to do with your actual data dependencies.
Keeping a list of referring rowids within a table B row would be hard
to maintain; you'd probably instead end up with a relationship table
that maps keys in table A to keys in table B -- the same thing you'd
use for a classic many-to-many relationship. I don't see this being a
win in any form, since it's essentially just a manual version of an
index on table A's refkey column.
I'd start with the no-extra-tracking approach, and only test other
methods if it turns out to be too slow for your data.
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
