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.