Subject Re: [firebird-support] Violation of UNIQUE KEY constraint during UPDATE
Author Ann W. Harrison
Helmut Doll wrote:
> Can anybody explain to me why the following simple UPDATE-Statement
> fails with the message "violation of PRIMARY or UNIQUE KEY constraint":
> UPDATE MY_TABLE SET IX = IX + 1 (if IX is a unique integer key, and
> the table contains a continuous sequence of values for IX)
That's a long-standing InterBase and Firebird bug. Actually, it's more
of a long-standing InterBase and Firebird behavior that became a bug
when the SQL standard was adopted. Even though the standard specifies
that the update is a single operation and the constraint is evaluated
after the operation, Firebird considers the update to be one operation
per record and complains if any intermediate state is invalid.

If you had stored the records in descending order by key, the operation
would work - not that that's any real answer, just another quirk.