Hello:
We found execution order is not associated with creation order, try this
(better in a new gdb)

CREATE TABLE T1 (
F1 INTEGER NOT NULL PRIMARY KEY
, F2 INTEGER
, F3 INTEGER
) ;
CREATE TABLE T2 (
F4 INTEGER NOT NULL PRIMARY KEY
, F5 INTEGER
) ;
COMMIT;
ALTER TABLE T1
ADD CONSTRAINT FK_F2_T2
FOREIGN KEY ( F2 )
REFERENCES T2 ( F4 ) ;
COMMIT;
insert into T2 values (2,3);
insert into T1 values (1,2,2);
COMMIT;

note constraint fk_f2_t2 uses index rdb$foreign3

if you try

delete from t2 where f4=2;

of course is a violation of fk_f2_t2

now you add

ALTER TABLE T1
ADD CONSTRAINT FK_F3_T2
FOREIGN KEY ( F3 )
REFERENCES T2 ( F4 )
ON DELETE cascade ;

note constraint fk_f3_t2 uses index rdb$foreign4, so, is created after
fk_f2_t2

if you try

delete from t2 where f4=2;

deletes both rows in db.

you can try inverse creation order of constraints fk_f3_t2 and fk_f2_t2 (and
test the order with rdb$foreignX) and the result is the same. We conclude
creation order of constrints is not relevant, and is possible FB executes
always cascade before restricted.

Note that cascade then restricted is inverse to sql99 spec.
(We like the suggestion of a field in system tables to specify exe order of
constraitns, is very flexible.)
[If system triggers are responsible for constraints, we think is not good
idea put constr before user triggers, for example to assign new.id =
generator + 1 in a before insert user trigger. This must be done before not
null constriaints ]

What order follows FB actually?

Thanks!
Regards

"Ann W. Harrison" wrote:

> At 07:58 PM 12/11/2002 -0300, IBUNC wrote:
> >This example shows ref integ exe order is important:
> >
> >
> >t1.f1 references t2 on delete [RESTRICTED]
> >t1.f2 references t2 on delete CASCADE
> >
> >delete from t2 where f3=2
> >
> >the transaction does not fail, and deletes both rows.
>
> >If constraints exe order is important ...
> >
> >What order follows FB?
>
> The order of trigger and constraint execution is controlled
> in the module dfw.epp, in the routine setup_triggers. It
> currently orders trigger execution with system triggers fired
> first, then user triggers in specified order, then constraints -
> I'd guess in order of creation, though that could easily be
> inverted.
>
> I suppose that the right answer is to allow the designer to
> specify the order in which constraints are to be evaluated,
> which requires a change to a system table, thus a new ODS.
> An alternative is to sort them by type, choosing some order.
> I haven't the foggiest notion what order to specify.
>
> Regards,
>
> Ann
>

Cobertura especial de la Copa Mundial de la FIFA Corea-Jap�n 2002, s�lo en Yahoo! Deportes:
http://ar.sports.yahoo.com/fifaworldcup/