On 23/09/2010 3:15 p.m., Igor Tandetnik wrote: > Josh Gibbs<jgi...@imailds.com> wrote: >> CREATE TABLE Message (message_id INTEGER PRIMARY KEY AUTOINCREMENT, >> Subject TEXT); >> CREATE TABLE MessageRecipient (message_id INTEGER REFERENCES >> Message(message_id) ON DELETE CASCADE, recipient_ID INTEGER REFERENCES >> Recipient(recipient_id)); >> CREATE TABLE Recipient (recipient_ID INTEGER PRIMARY KEY AUTOINCREMENT, >> name); >> >> I've tried creating a trigger after delete on MessageRecipient to remove the >> referenced Recipient, and this works if it's the only related item, >> however any >> other MessageRecipient relationship causes the delete to fail. As there >> is no >> 'or ignore' for the delete statement, I can't get this to keep my data >> clean. > You could do something like > > delete from Recipient where recipient_ID = old.recipient_ID and > recipient_ID not in (select recipient_ID from MessageRecipient); >
That was the last idea we had as well. Trouble is MessageRecipient contains hundreds of thousands of records. Would that cipple the speed of the delete, or would the query apply the 'not in' to the sub-select on its index? _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
