Alan i think a better approach might be to have triggers on yor tables to control what gets deleted when.
Woody Wizard, at large "I'm in shape, round is a shape!" --- On Sat, 7/7/12, Alan Chandler <a...@chandlerfamily.org.uk> wrote: From: Alan Chandler <a...@chandlerfamily.org.uk> Subject: [sqlite] Understanding Foreign Key Contraints To: sqlite-users@sqlite.org Date: Saturday, July 7, 2012, 4:32 PM I have a fairly complex web based application which helps manage the process of running an American Football results picking competition through the season. I am in the process of porting it to Sqlite ready for the start of next season. I just discovered a bug in my handling of a constraint violation that has made me want to rethink my strategy in this area. My original strategy was to completely cascade deletes, but the bug I discovered showed me a place in the user interface where that approach could be too dangerous, as it wasn't obvious that there would be side effects at the user interface level. So I want to replan my strategy. But given the complexity of the situation I want to make sure I don't make any mistakes. There is one particular pattern that occurs in several places, where what might happen is ambiguous (at least to me), and I would like this mailing lists view of what will happen and what is the right thing to do to make it so. [Note the application is web based with Ajax calls. Every single page request or ajax call opens the database and does a "PRAGMA foreign_keys = ON" as its first function] Let me list my key entities in this pattern At the top level there are three "Team" with primary key tid (which is a three character string - but that is probably irrelevant) "Participant" with primary key uid and "Competition" with primary key cid. There are then some secondary entities, for this example I need two "Registration" (user registers for a competition) which has primary key (cid,uid). Its foreign key constraints are defined as cid integer NOT NULL REFERENCES competition(cid) ON UPDATE CASCADE ON DELETE CASCADE, -- Competition ID uid integer NOT NULL REFERENCES participant(uid) ON UPDATE CASCADE ON DELETE CASCADE, --User ID "Team_in_competition" with primary key (cid,tid). Its foreign key constraints are defined as cid integer NOT NULL REFERENCES competition(cid) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, -- Competition ID tid character(3) NOT NULL REFERENCES team(tid) ON UPDATE CASCADE ON DELETE CASCADE, --TeamID And then a derived entity from the other two "Playoff_pick" with primary key (cid,uid,tid). Its the foreign key constrains on this one which is rather tricky I want to arrange my constraints so that. Deleting Competition or Participant Deletes everything below it Deleting Team_in_competition fails with a constrain violation when there is a playoff_pick that refers to it I am hoping that I can define the constraints so. FOREIGN KEY (cid,uid) REFERENCES registration(cid,uid) ON UPDATE CASCADE ON DELETE CASCADE, FOREIGN KEY (cid,tid) REFERENCES team_in_competition(cid,tid) What I am hoping is that if I delete the "Competition" (or "Participant") , then it deletes the "Registration" which in turn deletes the "Playoff_pick" immediately, but that because the deleting of "Team_in_competition" is deferred until commit time, by that time the commit happens there is no "Playoff_pick" to prevent the "Team_in_competition" from being deleted. Have I understood this right? -- Alan Chandler http://www.chandlerfamily.org.uk _______________________________________________ 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
