Re: [sqlite] many-one relation
A brute force method would be to store the rowid of the owner row in each of the detail rows. chetana bhargav 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 :) Sorry for the confusing message earlier -x- Chetana - Original Message From: Trevor Talbot <[EMAIL PROTECTED]> To: sqlite-users@sqlite.org Sent: Monday, October 8, 2007 9:10:22 PM Subject: Re: [sqlite] many-one relation On 10/8/07, chetana bhargav <[EMAIL PROTECTED]> wrote: We got two tables, tableA & tableB. tableB is turning out to be a many-one relation where in we have many rows of tableA mapping to one row of tableB, would like to know what is the best way to delete a row in tableB 1. Keep a reference count of the number of rows that are referring to this ( to be honest I dont think this is good) 2. More on similar lines instead of count have row-ids and attach a trigger I'm not clear on the scenario here. Is this a like a foreign key relationship, so you want a delete of a row in table B to delete all referring rows from A? Something else? - To unsubscribe, send email to [EMAIL PROTECTED] - Tonight's top picks. What will you watch tonight? Preview the hottest shows on Yahoo! TV. http://tv.yahoo.com/ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] many-one relation
I wrote: > 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 :) > 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. After thinking a bit more about implementing this, a pair of triggers can accomplish it without doing anything special in the app: CREATE TRIGGER DeRef AFTER DELETE ON A BEGIN UPDATE B SET refcount = refcount-1 WHERE key = OLD.refkey; END; CREATE TRIGGER DelOrphans AFTER UPDATE ON B WHEN NEW.refcount = 0 BEGIN DELETE FROM B WHERE rowid = NEW.rowid; END; I still favor the simpler approach, but at least this one is workable. I also choose lousy trigger names :) - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] many-one relation
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] -
Re: [sqlite] many-one relation
On Mon, 8 Oct 2007 09:04:53 -0700 (PDT), you 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 :) > >Sorry for the confusing message earlier > >-x- >Chetana This might be of help: http://www.sqlite.org/cvstrac/wiki?p=ForeignKeyTriggers http://www.rcs-comp.com/site/index.php/view/Utilities-SQLite_foreign_key_trigger_generator -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] many-one relation
>> 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 :) Sorry for the confusing message earlier -x- Chetana - Original Message From: Trevor Talbot <[EMAIL PROTECTED]> To: sqlite-users@sqlite.org Sent: Monday, October 8, 2007 9:10:22 PM Subject: Re: [sqlite] many-one relation On 10/8/07, chetana bhargav <[EMAIL PROTECTED]> wrote: > We got two tables, tableA & tableB. > > tableB is turning out to be a many-one relation where in we have many rows of > tableA mapping to one row of tableB, would like to know what is the best way > to delete a row in tableB > > 1. Keep a reference count of the number of rows that are referring to this ( > to be honest I dont think this is good) > 2. More on similar lines instead of count have row-ids and attach a trigger I'm not clear on the scenario here. Is this a like a foreign key relationship, so you want a delete of a row in table B to delete all referring rows from A? Something else? - To unsubscribe, send email to [EMAIL PROTECTED] - Tonight's top picks. What will you watch tonight? Preview the hottest shows on Yahoo! TV. http://tv.yahoo.com/
Re: [sqlite] many-one relation
On 10/8/07, chetana bhargav <[EMAIL PROTECTED]> wrote: > We got two tables, tableA & tableB. > > tableB is turning out to be a many-one relation where in we have many rows of > tableA mapping to one row of tableB, would like to know what is the best way > to delete a row in tableB > > 1. Keep a reference count of the number of rows that are referring to this ( > to be honest I dont think this is good) > 2. More on similar lines instead of count have row-ids and attach a trigger I'm not clear on the scenario here. Is this a like a foreign key relationship, so you want a delete of a row in table B to delete all referring rows from A? Is this a garbage collection situation, where you want a row in B to go away when all referring rows in A are deleted? Something else? - To unsubscribe, send email to [EMAIL PROTECTED] -
