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)
Ann W. Harrison answers:
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.