Update a primary key without triggering unique key violation

Question

I just came to this very simple situation where I needed to shift a primary key up a certain value. Suppose 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?

Answer 1

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.

Answer 2

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...