Yes, that would be nice. For example, sqlite already needs explicit opt-in for some of the relational toolkit. I think about "PRAGMA foreign_keys = ON".
Why not an opt-in way to ask for deferred constraint checking. The key here is only to allow perfectly legit requests to run. With all the due respect to sqlite implementors and the wonderful design of sqlite. > Le 8 déc. 2014 à 15:55, Nico Williams <n...@cryptonector.com> a écrit : > > Ideally there would be something like DEFERRED foreign key checking > for uniqueness constraints... You can get something like that by > using non-unique indexes (but there would also go your primary keys) > and then check that there are no duplicates before you COMMIT. (Doing > this reliably would require something like transaction triggers, which > IIRC exists in a "sessions" branch.) > > Nico > -- > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > >> Le 8 déc. 2014 à 10:55, Gwendal Roué <g...@pierlis.com> a écrit : >> >> 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. >> >> 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
