Thanks J T. Let's give a look. > Le 8 déc. 2014 à 14:24, J T <drenho...@aol.com> a écrit : > > Try having your cascade occur before the row is created, updated or deleted. > > http://www.sqlite.org/lang_createtrigger.html > > > > > > > > -----Original Message----- > From: Richard Hipp <d...@sqlite.org> > To: General Discussion of SQLite Database <sqlite-users@sqlite.org> > Sent: Mon, Dec 8, 2014 8:14 am > Subject: Re: [sqlite] sqlite bugreport : unique index causes valid updates to > fail > > > 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 for considering this issue. >> >> Cheers, >> Gwendal Roué >> >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > > > > -- > D. Richard Hipp > d...@sqlite.org > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
