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
