On 2014/12/08 11:55, Gwendal Roué wrote:
Hi,Unique indexes make some valid update queries fail. Please find below the SQL queries that lead to the unexpected error: -- The `books` and `pages` tables implement a book with several pages. -- Page ordering is implemented via the `position` column in the pages table. -- A unique index makes sure two pages do not share the same position. CREATE TABLE books ( id INT PRIMARY KEY ) CREATE TABLE pages ( book_id INT NOT NULL REFERENCES books(id) ON DELETE CASCADE ON UPDATE CASCADE, position INT ) CREATE UNIQUE INDEX pagination ON pages(book_id, position) -- Let's populate the tables with a single book and three pages: INSERT INTO books VALUES (0); INSERT INTO pages VALUES (0,0); INSERT INTO pages VALUES (0,1); INSERT INTO pages VALUES (0,2); -- We want to insert a page between the pages at positions 0 and 1. So we have -- to increment the positions of all pages after page 1. -- Unfortunately, this query yields an error: "columns book_id, position are not unique"/ UPDATE pages SET position = position + 1 WHERE book_id = 0 AND position >= 1;
NOT a bug... the moment you SET position to position +1 for the first iteration of the query, it tries to make that entry look like (0,2) and there is of course at this point in time already an entry like (0,2).
Some engines allow you to defer the constraint checking until the end of the transaction (and you can do this for References, though you are cascading which is fine). In SQLite the check is immediate and will fail for the duplication attempted on the first iteration. The fact that the other record will eventually be changed to no longer cause a fail is irrelevant to the engine in a non-deferred checking.
Now that we have established it isn't a bug, some methods of working round this exist, like Updating in the reverse order (though this has to be done in code as the UPDATE function cannot be ordered). Also creating a temp table then substituting it after an update (but then you have to recreate the index anyway, so dropping the index and re-making it is better though this can take a long time on really large tables).
My favourite is simply running the query twice, once making the values negative, and once more fixing them, like this: UPDATE pages SET position = ((position + 1) * -1) WHERE book_id = 0 AND position >= 1; UPDATE pages SET position = abs(position) WHERE book_id = 0 AND position < 0; No mess, no fuss, no Unique constraint problem. Cheers, Ryan _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
