increment contiguous unique values

I have contiguous sequential values in a UNIQUE KEY column:

mysql> SELECT foo FROM bar;

+-----+
| foo |

+-----+
| 1 |
| 2 |
| 3 |
| ... |

I want to insert a new row in the middle of this sequence, so I need to first shift all the rows which follow it.

So say I want to insert a new row as the second row in my example. I need to first increment the foo column for all rows where foo >= 2:

mysql> UPDATE bar SET foo = foo + 1 WHERE foo >= 2;

Duplicate entry '3' for key 1

I am confused by this error: If there are no duplicate values before my UPDATE statement, then there should be no duplicate values after it?

Do you need help?

In addition to our free technical library, Pantek provides professional IT services to companies who utilize Open Source software: Learn more about Pantek's Technical Support offerings Place an order for 24/7/365 Technical Support here Learn more about Pantek, who we are, and what we do Chat with a Live Operator Related lists.mysql.com Links announce benchmarks bugs certification cluster commits dotnet gui-tools internals java maxdb myodbc mysql mysqldoc packagers perl plusplus replication win32 Request more information about Pantek

I can only guess that MySQL is incrementing the second row before the third row, resulting in a temporarily duplicate value 3 in the foo column in the second and third row, and reporting an error?

I do want MySQL to prevent duplicate values in the foo column, however my UPDATE statement should not result in duplicate values. Is there any way to defer this constraint until after the statement completes?

I am using InnoDB...

Thanks and best wishes - Jack