9

I have a table in SQLite 3 thus:

sqlite> .schema
CREATE TABLE table1 (id INTEGER PRIMARY KEY NOT NULL,
                     title TEXT UNIQUE NOT NULL,
                     priority INTEGER UNIQUE NOT NULL);

Here is some sample data, for illustration:

sqlite> SELECT * FROM table1;
id          title       priority  
----------  ----------  ----------
1           a           1         
2           b           2         
3           c           3         
4           d           4

I wish to add 1 to the priority of all cells with priority > 1. Here is my first attempt:

sqlite> UPDATE table1 SET priority = priority + 1 WHERE priority > 1;
Error: column priority is not unique

This fails, presumably because the update is not ordered, allowing the UPDATE to attempt to set one of the cells in the priority column to the value of an existing cell. So, here is my second attempt:

sqlite> UPDATE table1 SET priority = priority + 1 WHERE priority > 1
        ORDER BY priority DESC;
Error: near "ORDER": syntax error

This, too, fails, presumably because my installation of SQLite 3 was not compiled with the SQLITE_ENABLE_UPDATE_DELETE_LIMIT option.

Because I might ultimately want to use my SQL statement(s) with SQLite on Android, and the latter also does not have SQLITE_ENABLE_UPDATE_DELETE_LIMIT enabled, I had better find another way to achieve my aim than re-compiling SQLite 3 with SQLITE_ENABLE_UPDATE_DELETE_LIMIT enabled. Here, then, is my third attempt:

sqlite> BEGIN TRANSACTION;
        UPDATE table1 SET priority = priority + 1 WHERE priority > 1;
        END TRANSACTION;
Error: column priority is not unique

This, too, fails, presumably because SQLite checks the uniqueness constraint before committing the transaction.

Three failed attempts, but I'm sure it's possible. The questions are: how to do it; and within that, how best to do it?

N.B. I would prefer not to rely upon any unchecked assumptions.

Community
  • 1
  • 1

1 Answers1

12

You are right that the problem appears because SQLite checks the constraints after every row update and not at the end of statement or the end of transaction.

I see this workaround to the problem (of SQLite not having implemented UPDATE correctly). Assuming that the priority column does not have any negative values, we can use them (negative values) as temporary to avoid the UNIQUE constraint errors:

UPDATE table1 SET priority = - (priority + 1) WHERE priority > 1 ;

UPDATE table1 SET priority = - priority WHERE priority < 0 ;
ypercubeᵀᴹ
  • 113,259
  • 19
  • 174
  • 235
  • Interesting; thanks! I don't yet know whether the assumption that `priority` will not contain negative values, will hold true; so I although I have upvoted your answer, I have not marked it "accepted". I had been thinking of trying a COUNT to establish an offset to avoid collisions, but the success of that approach would depend on a different assumption (namely that no value in `priority` is any greater than it would be if the values in `priority` were guaranteed consecutive), the correctness of which I am again uncertain about. I would prefer a solution that does not rely upon assumptions. –  Oct 15 '13 at 12:57
  • (Or, more specifically, on unchecked assumptions.) –  Oct 15 '13 at 13:07
  • I thought you are the designer or developer of this database/application so you know the requirements/assumptions. – ypercubeᵀᴹ Oct 15 '13 at 14:20
  • This is a known issue, but not considered as a bug : http://www.sqlite.org/src/tktview?name=fc6f41426b – Vincent Jan 22 '14 at 17:01
  • 4
    I'm really sorry to see this closed as "not a bug". They should also consider changing this line from the About SQLite page: *"SQLite is an in-process library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine."* It cannot be considered a transactional SQL database engine until it complies (or tries to comply) with the SQL standards - which specifically state that constraints should be checked at the end of statements (or if deferred) at the end of transactions. – ypercubeᵀᴹ Jan 22 '14 at 17:10