Update a primary key without triggering unique key violation

I just came to this very simple situation where I needed to shift a primary key up a certain value. Supp开发者_运维百科ose the following table:

CREATE TABLE Test (
 Id INTEGER PRIMARY KEY,
 Desc TEXT);

Loaded with the following values:

INSERT INTO Test VALUES (0,'one');
INSERT INTO Test VALUES (1,'two');

If there's an attempt at updating the primary key, it will, of course, fail:

UPDATE Test SET Id = Id+1;

Error: column id is not unique

Is there some way to suspend unicity check until after the update query has run?

Find a nice pivot point, and move the data around that pivot. For example, if all your IDs are positive, a good pivot is 0.

When you would normally do

UPDATE Test SET Id = Id+1;

Do this sequence instead

UPDATE Test SET Id = -Id;
UPDATE Test SET Id = -Id +1;

For times, you can find a similar pivot point, but the formula is just a tad harder.

without understanding the fundamental problem (and yeah, you seem like a victim of code and run on this one!), multiplying the ID by the largest value in the table should work.

update test
set id = id * (select max(id) + 1 from test)

However, it's dirty, and really, databases make it hard to change primary keys for a reason...

OK. Second attempt. Try this:

1. Get the MAX of the key column.
2. UPDATE table SET key = key + max + 1
3. UPDATE table SET key = key - max

This will avoid duplicated keys at any time in the update process by moving the window far enough.