SQLite increment Integer Primary Key and Unique Constraint conflict

Question

I have a SQLite database. When writing move rows function, which moves rows from one table to another I need to have a query for incrementing column with name "row" which is INTEGER PRIMARY KEY, but there is an error. It is critical to have indexing with row in my task. The condition in example is WHERE row >= 2 because i am inserting rows from other table into position 2.

"UPDATE '4' SET row = row + 1 WHERE row >= 2"

Error("19", "Unable to fetch row", "UNIQUE constraint failed: 4.row")

The problem's origin WHERE row >= 2" part. How to overcome this problem?

Answer 1

First: '4' is not a table name. The UPDATE statement expects a table name where you have written '4'. For example:

UPDATE table1 SET row = row + 1 WHERE row >= 2

Second: Just do not use row as a primary key (or unique key, for that matter) when it obviously is not meant as primary key but as a changing row number. Create a separate column that can be used as primary index of that table instead.

Answer 2

The problem's origin WHERE row >= 2" part.

I'm inclined to disagree. The problem is not with which rows are updated, it is with the order in which they are updated.

Very likely SQLite will process rows in rowid order, which almost certainly is also increasing order of the row column, since that column is an auto-incremented PK. Suppose, then, that the table contains two rows with row values 2 and 3. If it processes the first row first, then it attempts to set that row's row value to 3, but that produces a constraint violation because that column is subject to a uniqueness constraint, and there is already a row with value 3 in that column.

How to overcome this problem?

Do not modify PK values, and especially do not modify the values of surrogate PKs, which substantially all auto-increment keys are.

Alternatively, update the rows into a temporary table, clear the original table, and copy the updated values back into it. This can be extremely messy if you have any FKs referencing this PK, however, so go back to the "Do not modify PK values" advice that I led off with.