Hi all, I'm hoping someone can assist me with a problem I'm having creating a cascading delete operation as well as a constraint.
This table is an example of the layout of my data: 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); My goal is that when I remove an item from the Message table, all of the related items in the MessageRecipient table are removed, and all items from the Recipient table that no longer have references to the MessageRecipient are removed. The tables as listed above fulfill the need for a Recipient to exist, prevent a Recipient from being removed if a MessageRecipient still points to them, and removes the MessageRecipient when the Message is deleted. It does not fulfill tidying up of the Recipient table, and items are left orphaned when all Messages are removed that refer to that recipient. 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. Any solutions to this conundrum would be greatly appreciated. Thanks, Josh _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
