***BOGO*** increment contiguous unique values

[Jack Bates <ms419@xxxxxxxxxxxxxx>]

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?

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

Attachment: signature.asc
Description: This is a digitally signed message part