Re: [sqlite] DELETE OR IGNORE statement?
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
Re: [sqlite] DELETE OR IGNORE statement?
On Friday, September 24, 2010 1:03 PM, Josh Gibbs wrote: What's the chance that 2 people with the same surname would have the same problem in the same week... [ ... ] I can only think its something in the name! I did actually see your thread moments after creating my own. I'd done a search on DELETE OR IGNORE without finding yours, so that'll teach me not to check more thoroughly!!! ;o) I did some further tests though and found that even if a DELETE OR IGNORE existed it probably would not do what I would have expected it to do, since foreign key constraints cause an ABORT regardless of the conflict resolution given in the statement. I found that an INSERT/UPDATE OR IGNORE will also abort with an error if the foreign key constraints fail, rather than just not performing the action. Maybe my understanding/intuition of the OR IGNORE clause should do is somewhat adrift of what it should be. Cheers Andy ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] DELETE OR IGNORE statement?
Hi, 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 hence stopping the deletion of any row from the primary key table if there exists any rows in any of the foreign key tables that reference that row. All well and fine, but what I'd really like is to be able to tidy up the primary key table when rows are no longer referenced by any foreign key table (e.g. following deletes in a foreign key table). At this point, a simple DELETE OR IGNORE FROM pkey_table should have sufficed. However, I was surprised to discover that DELETE OR IGNORE is not an option. (I guess it may not even be standard SQL - I don't know, but it seems an odd omission if so!) Anyone got a better way of doing this? I've thought about a mammoth DELETE FROM pkey_table WHERE pkey NOT IN (SELECT fkey FROM fkey_tab1 UNION SELECT fkey FROM fkey_tab2 UNION SELECT ... UNION SELECT ... ... ...), but the problem is that there are quite a number of foreign key tables (and more get added from time to time) and I'd really like something I can stick in a AFTER DELETE trigger on each of the foreign key tables without the problem of having to update all the triggers each time a table is added/removed. Thanks for any suggestions! Andy ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] DELETE OR IGNORE statement?
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. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] DELETE OR IGNORE statement?
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
Re: [sqlite] DELETE OR IGNORE statement?
On 23 Sep 2010, at 2:59pm, Andy Gibbs wrote: 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. Hmm. Yes, I think so. Some part of your code somewhere has to list all the secondary tables, and you already have it in the least annoying place. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
