Incrementing value in table with unique key causes constraint error

Question

I have a table created with

CREATE TABLE `tests` (`num` INTEGER UNIQUE, value VARCHAR(255))

and I add two rows to it with:

INSERT INTO `tests` VALUES(0, "a")
INSERT INTO `tests` VALUES(1, "b")

My goal is now to insert a row with num = 0, but to be able to do that, I need to shift up the index on those two rows.

When I execute:

UPDATE `tests` SET `num` = `num` + 1 WHERE `num` >= 0

I get the error:

could not execute statement due to a constaint failure (19 constraint failed)

My guess is that it is incrementing each row, and when it goes to increment the first one by setting it to 1, there is already a row with the num of 1, thus failing.

In standard code this would be solved by iterating backwards over the collection, is there any way to do that in Sqlite? Is there a different way to go about this?

Answer 1

The updates will work if you do not do them in place but with the help of a temporary table:

CREATE TEMP TABLE t2 AS SELECT * FROM tests WHERE num >= 0;
DELETE FROM tests WHERE num >= 0;
INSERT INTO tests SELECT num + 1, value FROM t2;
DROP TABLE t2;

Alternatively, use an increment that is large enough that there will be no conflicts. You have then to do this again to get back to the desired values:

UPDATE tests SET num = num + 3     WHERE num     >= 0;
UPDATE tests SET num = num - 3 + 1 WHERE num - 3 >= 0;