I just thought of what may be a simpler solution.
I'm assuming that there is a certain limit to the length of the books
(positions can be safely assumed to never exceed say, 100,000)
So what can be done is
update page set position=position + 100000 where position>='3';
insert into page(book,position) values('1','3');
update page set position=position - 99999 where position>100000;
This will work around the unique contraint and seems simpler than dropping it
everytime you want to insert a page.
-----Original Message-----
From: Gwendal Roué <g...@pierlis.com>
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
Sent: Mon, Dec 8, 2014 12:07 pm
Subject: Re: [sqlite] sqlite bugreport : unique index causes valid updates to
fail
> Le 8 déc. 2014 à 17:21, Simon Slavin <slav...@bigfraud.org> a écrit :
>
>> 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.
>
> SQL-99 includes a syntax for deferred checking. We don't need to invent our
own syntax with a PRAGMA. However, it is done when the constraint is defined
rather than being something one can turn on or off. So you would need to think
out whether you wanted row- or transaction-based checking when you define each
constraint in the first place.
Hi Simon,
This topic is fascinating. Googling for SQL-99 deferred checking, I stumbled
upon this page which shows how deferred index maintenance affects Oracle query
plan, and performance :
https://alexanderanokhin.wordpress.com/deferred-index-maintenance/.
I now understand that the strategy for checking index constraints is tied to
their maintenance.
The `UPDATE pages SET position = position + 1 WHERE book_id = 0 AND position >=
1` query we are talking about has indeed to perform both. Such an
innocuous-looking request, and it sends us right into the very guts of
relational constraints :-)
Gwendal
_______________________________________________
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
