dimanche 14 février 2016

SQLITE: Transaction to check constaints after commit

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 commited?

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

Aucun commentaire:

Enregistrer un commentaire