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
