Updating unique columns [feature request]

(1) By tom (younique) on 2020-06-08 20:39:24 [link] [source]

Let's assume we have a table with a unique column named "id" with values 1, 2, 3, 4, 5.

It is no problem to run

UPDATE table SET id=id-1

This is because reassigning numbers (1->0, 2->1, 3->2,...) never causes a conflict. The next number required will always be available.

However, this one fails:

UPDATE table SET id=id+1

Of course, because the first reassignment (1->2) will be carried out while number 2 is still in use, thus leading to a duplicate key error.

It would be very helpful if SQLite could do the update in a way that prevents duplicate key errors. From the "atomic" point of view, there is no conflict at all because all values are updated simultaneously.

(2) By Warren Young (wyoung) on 2020-06-08 20:45:24 in reply to 1 [link] [source]

Are you trying to reinvent autoincrement?

(3) By Larry Brasfield (LarryBrasfield) on 2020-06-08 21:03:29 in reply to 2 [link] [source]

He wants to do the equivalent of a multiple assignment, where the sequence of values (1, 2, 3, 4, 5) becomes the new sequence (2, 3, 4, 5, 6).

To the OP: Your update would succeed if done in the opposite order. Whether that can be made to happen reliably is an issue, but no more of an issue than with your example that does happen to work (SET id=id-1) by accident of the order in which the updates occur.

(4) By Simon Slavin (slavin) on 2020-06-08 23:45:47 in reply to 1 [link] [source]

I am alarmed that you are changing numbers in a column called 'id'. But let's ignore that for the sake of argument.

How should this work ? Should SQLite ignore existing values for all rows where it intends to replace the value ?

(5) By Richard Damon (RichardDamon) on 2020-06-09 00:47:45 in reply to 4 [source]

It might be possible, or made to be possible, to delay the checking of a unique constraint till commit time, sort of like can be done with Foreign Key constraints.

I could see a difficulty if the field was an INTEGER PRIMARY KEY, as that would be the rowid, and that I could see would need to be always unique. There might also be difficulaties if these exist foreign keys with a CASCADE UPDATE condition on them.

(6) By Mark Lawrence (mark) on 2020-06-16 19:24:57 in reply to 1 [link] [source]

You can use UPSERT[1] to force the order in which UPDATES are performed.

CREATE TABLE t1(id INTEGER);
INSERT INTO t1 VALUES (1), (2), (3);
SELECT * FROM t1;
-- 	id
-- 	--
-- 	1
-- 	2
-- 	3

INSERT INTO t1( rowid, id)
SELECT rowid, id + 1
FROM t1
WHERE 1      		-- Some kind of WHERE needed for UPSERT parsing
ORDER BY id DESC	-- INSERT and therefore also UPDATE order
ON CONFLICT(rowid) DO UPDATE SET id = excluded.id;

SELECT * FROM t1;
-- id
-- --
-- 2
-- 3
-- 4

[1] https://www.sqlite.org/lang_UPSERT.html