SQLITE: Transaction to check constraints after commit

Question

I'm currently working on an sqlite table where I have to do the following:

ID | Name  | SortHint
---|-------|---------
 0 |   A   |    1
 1 |   B   |    2
 2 |   C   |    3

ID is the primary key and SortHint is a column with the UNIQUE-constaint. What I have to do is to modify the table, for example:

ID | Name  | SortHint
---|-------|---------
 0 |   A   |    3
 1 |   B   |    1
 2 |   C   |    2

The Problem: Because of the UNIQUE I can't simply update one row after another. I tried:

BEGIN TRANSACTION;
UPDATE MyTable SET SortHint = 3 WHERE ID= 0;
...
COMMIT;

But the first update query immideatly fails with:

UNIQUE constraint failed: MyTable.SortHint Unable to fetch row

So, is there a way to "disable" the unique constaint for a transaction and only check all of them once the transaction is committed?

Notes:

• I can't modify the table
• It works if I only use SortHint values that are not already in the table
• I know how to "workaround" this problem, but I would like to know if there is a way to do this as described above

Answer 1

One possibility is to drop the unique constraint and then add it again. That is a little bit expensive, though.

Another would be to set the values to negative values:

UPDATE MyTable
    SET SortHInt = - SortHint;

UPDATE MyTable
    SET SortHint = 3
    WHERE ID = 0;

. . .

Answer 2

If you cannot modify the table, you are not able to remove the constraint. A workaround could be to change the SortHint to a range that is not in use. For example you could add 10,000 to all of them. Commit. Then change to the right number at once which have become free now. Maybe test afterwards that no numbers of 10,000 or higher exist anymore.