--- In firebird-support@yahoogroups.com, "Ann W. Harrison"
<aharrison@...> wrote:

>
> Adam wrote:
>
> >
> > Cascade any updates to the ID field in the people table to the
> > peopleID field in the clients table. Cascade any deletes of records
> > in the people table by removing any records from the clients table
> > with that peopleID.
> >
> > It means that if you run:
> >
> > update people
> > set ID = ID + 1;
> >
> > Firebird will automatically update all the records in the clients
> > table to reflect the new ID, so that the records in the clients table
> > are still pointing to the same person (who now has a new ID).
>
> That's a correct description of Cascade, but the actual example
> won't work (probably) because Firebird's implementation of
> constraint evaluation on update is not correct. According to
> the standard, constraints are evaluated at the end of the verb.
> Firebird evaluates them when the row is changed.
>
> Specifically, the referenced field in a foreign key constraint
> (id in people in this case) must be subject to a primary key
> or unique constraint. That means that if you have people with
> ids from 1 to 100, and you modify them by incrementing the id
> by one, Firebird checks that the value of the id field is unique
> after each row is changed. So, unless you're extremely lucky
> and update the rows in descending order, one of the intermediate
> results will violate the constraint and the whole update will
> fail.

It would work if there was only one record ;)

I never encounter this problem because none of my primary keys have
any real world significance. They are values dished out by a
generator. There is no need to ever update them.

Sadly, deferred constraints is a low priority for now (Roadmap 06),
but a good eye for picking up on that.

Adam