What's the chance that 2 people with the same surname would have the same problem in the same week...
I believe I just solved the same problem you are asking about yesterday thanks to a query from Richard: 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. > > DELETE FROM recipient > WHERE recipient_id = old.recipient_id > AND NOT EXISTS(SELECT 1 FROM message_recipient > WHERE recipient.recipient_id= > message_recipient.recipient_id); > That SQL statement (with minor corrections) works within and AFTER DELETE trigger. The key references prevent deletion, and the trigger does the cleanup when only one item is left over. I struggled the same way you did at first thinking there would be a DELETE OR IGNORE clause. Hope this helps. Josh On 24/09/2010 1:59 a.m., Andy Gibbs wrote: > On Thursday, September 23, 2010 3:36 PM, Simon Slavin wrote: > >> On 23 Sep 2010, at 2:13pm, Andy Gibbs wrote: >> >>> I've got a table with a primary key and then any number of additional >>> tables >>> with foreign keys that reference this primary key table with "ON DELETE >>> RESTRICT" >> I always worry when I see descriptions like this. Those additional >> tables: do they all have the same columns ? If so, can you amagamate >> them all into one big table ? Just insert one extra column saying what >> kind of row this row is. >> >> Not only does this fix the problem you raised, but it means you don't >> need to change your schema each time you encounter a new type of >> information. > Thanks for the suggestion, Simon. If only it were that simple. > Unfortunately, each of the foreign key tables are actually quite distinct in > their purpose, so putting them all into one huge table would not be the > right solution. > > The primary key is a timestamp (as an integer, i.e. number of seconds since > some arbitrary epoch or other). The primary key table holds then the > "common" information on the "action" that has happened, i.e. timestamp, user > name, and some other data. The foreign key tables are all those that hold > data for the particular actions that can be done, but really they are very > very different from each other. > > Of course it would have been possible instead to merge the columns from the > primary key table into each of the foreign key tables and not have the > primary key table, but the really nice thing about keeping the common data > it central, is that only one table needs to be queried e.g. to find out the > which users have been making alterations to the system and when (this is one > of the main design requirements). > > It seems to be a trade-off -- either the complexity is in the DELETE > statement to keep the primary key table tidy or in the SELECT statement > querying it. If it has to be a choice, then the complexity has to be in the > DELETE statement since this happens very infrequently. > > Cheers > Andy > > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
