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
