Thread: Delete Order When Referential Integrity Is Active
(Please first pardon me for the repost as my previous post to google groups seems to have not been populated to this mailing list.) Hi! Is it guaranteed or not to delete the rows from referencing table before the referenced table when referential integrity constraint is applied? For example: CREATE TABLE MasterTable (mc1 TEXT PRIMARY KEY, UnitPrice INTEGER); CREATE TABLE DetailTable ( dc1 TEXT ,customer TEXT ,quantity INTEGER ,PRIMARY KEY (dc1,customer) ,CONSTRAINT MyConstraint FOREIGN KEY (dc1) REFERENCES MasterTable (mc1) ON UPDATE CASCADE ON DELETE CASCADE ); Now, when a SQL like DELETE FROM MasterTable WHERE mc1 = 'a'; is issued, will the rows in DetailTable be deleted before the row in MasterTable having the value 'a' in both mc1 and dc1? I am asking this unusual question because I have a delete trigger associated with DetailTable to be fired before the delete from MasterTable. As in this example, if master row is deleted before detail rows, then this trigger function, associated with DetailTable, will be unable to find UnitPrice in MasterTable. If the answer is negative, then my next question would be: "Is there any plan to implement this feature?" I would be grateful for any instruction. CN-- http://fastmail.fm - Choose from over 50 domains or use your own
On Mon, 25 Nov 2002, CN wrote: > Is it guaranteed or not to delete the rows from referencing table before > the referenced table when referential integrity constraint is applied? > For example: > > CREATE TABLE MasterTable (mc1 TEXT PRIMARY KEY, UnitPrice INTEGER); > > CREATE TABLE DetailTable ( > dc1 TEXT > ,customer TEXT > ,quantity INTEGER > ,PRIMARY KEY (dc1,customer) > ,CONSTRAINT MyConstraint FOREIGN KEY (dc1) REFERENCES MasterTable (mc1) > ON UPDATE CASCADE ON DELETE CASCADE > ); > > Now, when a SQL like > > DELETE FROM MasterTable WHERE mc1 = 'a'; > > is issued, will the rows in DetailTable be deleted before the row in > MasterTable having the value 'a' in both mc1 and dc1? No. The MasterTable row will be deleted first. > I am asking this unusual question because I have a delete trigger > associated with DetailTable to be fired before the delete from > MasterTable. As in this example, if master row is deleted before detail > rows, then this trigger function, associated with DetailTable, will be > unable to find UnitPrice in MasterTable. > > If the answer is negative, then my next question would be: > > "Is there any plan to implement this feature?" We've had some various discussion about when dependent rows get deleted, but the working definition right now is that it occurs at the time at which a check would have been done were it a no-action constraint. The other proposed definition was that it was effectively *part* of the deletion of the other row, so I think that in neither case would it get the behavior you're looking for. I haven't really looked to see if there's anything in the spec about the timing of dependent deletes as associated with triggers, though.
----- Original Message ----- From: "Stephan Szabo" <sszabo@megazone23.bigpanda.com> Sent: Tuesday, November 26, 2002 4:23 AM > the behavior you're looking for. I haven't really looked to see if > there's anything in the spec about the timing of dependent deletes as > associated with triggers, though. There is something in the docs about tuple visibility that states, "if a query affects a row, each query in time after this one, no matter if it's triggered by the original query or it's an independent query, sees the new state of the tuple" or something like that. Doesn't it apply to this case? I mean, reference is effectively a trigger, something like ... AFTER DELETE ON MasterTable FROM DetailTable ... so the DELETE's on DetailTable _must_ follow (in time) the DELETE on MasterTable. Maybe it's the exact cause that the master row is deleted first. Gurus, please teach me something and tell me if my thoughts are right or not :) G. -- while (!asleep()) sheep++; ---------------------------- cut here ------------------------------
On Wed, 27 Nov 2002, [iso-8859-1] SZUCS G�bor wrote: > ----- Original Message ----- > From: "Stephan Szabo" <sszabo@megazone23.bigpanda.com> > Sent: Tuesday, November 26, 2002 4:23 AM > > > > the behavior you're looking for. I haven't really looked to see if > > there's anything in the spec about the timing of dependent deletes as > > associated with triggers, though. > > > There is something in the docs about tuple visibility that states, "if a > query affects a row, each query in time after this one, no matter if it's > triggered by the original query or it's an independent query, sees the new > state of the tuple" or something like that. > > Doesn't it apply to this case? I mean, reference is effectively a trigger, > something like > > ... AFTER DELETE ON MasterTable FROM DetailTable ... > > so the DELETE's on DetailTable _must_ follow (in time) the DELETE on > MasterTable. Maybe it's the exact cause that the master row is deleted > first. That's why it does in practice. The question is whether that is compliant behavior to the spec which I believe we are for this case although I'm not sure about all of the cases.