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?

Check this out: http://stackoverflow.com/questions/1884787/how-do-i-drop-a-constraint-from-a-sqlite-3-6-21-table. — Gordon Linoff, Mar 19 '14 at 01:06

score 6 · Accepted Answer · answered Mar 19 '14 at 08:51

6

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;

answered Mar 19 '14 at 08:51

CL.

173,858
17
217
259

1

This is a good answer that someone else might want in the future, but won't work in my case. This update happens fairly often as it's trying to have a crude ordered list that needs an insert operation in the middle so the temp table isn't reasonable. The double update trick is a neat one, hadn't thought of that. The answer I went with is to drop the constraint and make sure my code is well tested. – Eli White Mar 20 '14 at 00:23

score -1 · Answer 2 · answered Mar 19 '14 at 09:38

-1

I propose to use IDENTITY constraint. Incrementing is being done automatically with this constraint.

CREATE TABLE test
(
    ID int IDENTITY(1,1) PRIMARY KEY UNIQUE,
    value VARCHAR(255)
)

Then you can write INSERT INTO test (value) values ('a') and you do not have to worry about incrementing.

Identity (1,1) is not necessary (default if you just write identity), but you can try other combinations like IDENTITY(int a, int b) - then you start with a and increment by b value.

answered Mar 19 '14 at 09:38

user3162968

1,016
1
9
16

I commented that my goal is to insert at the beginning, therefore Identity is not reasonable as that will give me a number greater than any number currently in the table. I need to actually change the values. – Eli White Mar 20 '14 at 00:25

Incrementing value in table with unique key causes constraint error

2 Answers2