Bug #7290 update should check primary keys after all records altered
Submitted: 14 Dec 2004 18:27 Modified: 14 Dec 2004 19:19
Reporter: Yuliy Pisetsky
Status: Open
Category:Server: InnoDB Severity:S4 (Feature request)
Version:4.1.7 OS:Sun Solaris (Solaris)
Assigned to: Heikki Tuuri Target Version:
Triage: D5 (Feature request)

[14 Dec 2004 18:27] Yuliy Pisetsky 
Description:
I have a table which has auto-incremented keys. I want to bump each of them up 1 index. I
try to do a "UPDATE foo SET id=id+1;" It gives error about a duplicate key. Such a linear
transformation cannot result in duplicate keys if they were unique before. 

How to repeat:
create an auto-incremented table, add at least two records. execute "UPDATE foo SET
id=id-1;UPDATE foo SET id=id+1;"

Suggested fix:
if a pk being changed conflicts with another record, before erroring out, check to see if
that other record is affected by the update, if so, handle it and then redo the check.
[14 Dec 2004 18:52] Yuliy Pisetsky 
adding version info
[14 Dec 2004 19:19] Heikki Tuuri 
Hi!

This is a known deviation of MySQL from SQL standards. I am changing this to a feature
request, since this requires quite a bit of work to fix.

Regards,

Heikki
[10 Oct 2005 21:07] Scott Marlowe 
Note that the standard work around for this is to do something like:

update table set id=id+somebignumber;
update table set id=id-(somebignumber-1);

This is the same thing I have to do with PostgreSQL and a few other databases.