Subject Re: [firebird-support] Re: Updating a stored procedure
Author Ann W. Harrison
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