At 07:24 PM 2/09/2003 +0200, you wrote:

>hello all,
>
>I have created a database that is working fine. This is a little
>extract (meta code, not real SQL code) of the FK it contains:
>
>Table 3 FK, references table 1 on delete cascade
>Table 4 FK, references table 1 on delete cascade
>Table 5 FK, references table 1 on delete cascade
>Table 2 FK, references table 1 on delete cascade
>
>It happened that I moved the last FK at the first place into the
>script and I created the database again from scratch:
>
>Table 2 FK, references table 1 on delete cascade
>Table 3 FK, references table 1 on delete cascade
>Table 4 FK, references table 1 on delete cascade
>Table 5 FK, references table 1 on delete cascade
>
>When I delete a row in table 1 I get a constraint violation on a field
>in table 5 that can't be null. At a first look, the error is generated
>in the after delete trigger for table 2 when the totals in table 5 are
>going to be automatically updated after some calculations.
>
>If I return back to the original order of FK's, the row deletion in
>table 1 and the consequent cascades are executed without errors.
>
>So I was wondering if creation order is important, especially when
>after delete triggers are used in tables where FK's are also used. I
>have found nothing in the documentation (Interbase 6 manuals) about
>this argument.

AFAIK, the creation order of the FKs doesn't matter. Certainly, in terms
of design integrity, it is wrong to base the logical design on the
assumption that parallel cascades will be done in a special order. It
tempts you to introduce circular dependencies like the one you have between
table2 and table5 in this case.

Another reason why a violation like this occurs is that the cascade
encounters a child row that is involved in an unresolved transaction, even
if indirectly, through a less visible dependency.

It's also possible that the result of your test on the creation order of
the constraints is coincidental - simply, in order to drop and recreate the
constraints, the database had to be in a shutdown state. You then started
operating again in a clear environment and got the "right" answer for the
wrong reason.

If this sort of thing occurred in one of my dbs, I'd want to walk through
the entire dependency chain and find out whether a failing cascade
operation was what I really wanted. It might be quite OK: you can
intercept the error and tell the user she can't do this delete/update until
<something else happens>. On the other hand, if it occurs because of an
overlooked dependency and creates an impossible bottleneck, I'll need to
handle the situation in a different way.

A very handy piece of documentation you can keep is an annotated tree
diagram of all of your dependencies. Use e.g. solid lines to represent the
parent-child ("vertical") dependencies and dashed lines to represent
"horizontal" dependencies - those that are not part of the tree hierarchy -
such as triggers, stored procedures as well as those
disasters-waiting-to-happen, computed columns and check constraints that
target foreign objects.

Draw a circle around any place where a hierarchy (vertical) chain either
branches or intersects with a dashed line - those are places where
cascading ref. integrity is likely to cause more problems than it
solves...in your current case, the circularity would be obvious.

heLen