Re: [sqlite] Trouble with constraints and triggers

2010-09-23 Thread Josh Gibbs
On 23/09/2010 11:52 p.m., Richard Hipp wrote: >> Josh Gibbs 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,

Re: [sqlite] Trouble with constraints and triggers

2010-09-23 Thread Igor Tandetnik
Josh Gibbs wrote: > On 23/09/2010 3:15 p.m., Igor Tandetnik wrote: >> 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

Re: [sqlite] Trouble with constraints and triggers

2010-09-23 Thread Richard Hipp
On Wed, Sep 22, 2010 at 11:15 PM, Igor Tandetnik wrote: > Josh Gibbs wrote: > > CREATE TABLE Message (message_id INTEGER PRIMARY KEY AUTOINCREMENT, > > Subject TEXT); > > CREATE TABLE MessageRecipient (message_id INTEGER REFERENCES > > Message(message_id)

Re: [sqlite] Trouble with constraints and triggers

2010-09-22 Thread Josh Gibbs
On 23/09/2010 3:15 p.m., Igor Tandetnik wrote: > Josh Gibbs 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

Re: [sqlite] Trouble with constraints and triggers

2010-09-22 Thread Igor Tandetnik
Josh Gibbs 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

[sqlite] Trouble with constraints and triggers

2010-09-22 Thread Josh Gibbs
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