> Le 8 déc. 2014 à 14:14, Richard Hipp <d...@sqlite.org> a écrit : > > On Mon, Dec 8, 2014 at 4:55 AM, Gwendal Roué <g...@pierlis.com> 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; >> >> The query should run without any error, since it does not break the unique >> index. >> > > Uniqueness is checked for each row change, not just at the end of the > transaction. Hence, uniqueness might fail, depending on the order in which > the individual rows are updated.
Thank you Richard for your answer. We share the same conclusion. I even tried to decorate the update query with "ORDER" clauses, in a foolish attempt to reverse the ordering of row updates, and circumvent the issue. Our analysis describes an implementation detail. Still, this behavior can not be considered as normal, and closed as "behaves as expected". I still believe that my initial mail is an actual bug report and should be treated as such. I hope it will find an interested ear. I'm unfortunately not familiar enough with the sqlite guts to fix it myself - especially considering the root cause. Messing with relational constraints validation is not an easy task. Regards, Gwendal Roué _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
