On Mon, 08 Dec 2014 15:48:41 +0200
RSmith <rsm...@rsweb.co.za> wrote:
> > 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).
Yes, that's how SQLite works, or doesn't. Whether or not it's a bug
depends on how you define the term.
The issue has come up here before: contrary to the SQL standard, SQLite
does not support constraint enforcement with transaction semantics.
I've never heard of another SQL DBMS that behaves that way.
sqlite> create table T (t int primary key);
sqlite> insert into T values (1), (2);
sqlite> update T set t = t+1;
Error: column t is not unique
As the OP discovered, the one recourse is to relieve the constraint
during the update. Another is to update a temporary table, and then
delete & insert the rows in a transaction. I would say "must implement
one's own transaction semantics" is, if not a bug, at least a
misfeature.
--jkl
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
