>I found the the restriction of updating unique column in ver3.6.21.
>and same problem is reported in follwoing mail
>
>Marc-Andre Gosselin wrote:
>date: Thu, 16 Jun 2005
>title: "[sqlite] Update unique column"
> >
> > I discovered a behavior in SQLite 2.8.16 that doesn't conform to
> the SQL
> > standard, here's an example :
> >
> > CREATE TABLE tbUpdateUnique (a INTEGER PRIMARY KEY, b INTEGER UNIQUE, c
> > VARCHAR(100));
> > INSERT INTO tbUpdateUnique VALUES('', 1, "Test 1");
> > INSERT INTO tbUpdateUnique VALUES('', 2, "Test 2");
> > INSERT INTO tbUpdateUnique VALUES('', 3, "Test 3");
> >
> > Now when I try the following update, I get a constraint error :
> >
> > UPDATE tbUpdateUnique SET b = b + 1 WHERE b >= 2;
> >
> > In the SQL Standard and NIST SQL test suite they say than an update
> > should be considered atomic, and verify unique constraints only
> after the operation has
> > updated all rows. From what I experienced with SQLite, constraints
> are verified
> > after each row has been updated, resulting in a constraint error. I
> also tried
> > these with no success :
> >
> > BEGIN TRANSACTION;
> > UPDATE tbUpdateUnique SET b = b + 1 WHERE b >= 2;
> > COMMIT TRANSACTION;
> >
> > and
> >
> > UPDATE tbUpdateUnique SET b = b + 1 WHERE a IN (SELECT a FROM
> tbUpdateUnique
> > WHERE b >= 2 ORDER BY b DESC);
> >
>
># full mail can be seen in archieve log at
># http://www.mail-archive.com/sqlite-users@sqlite.org/msg08597.html
>
>Will this restriction be fixed in near feature?
>
>I also know the workaround to use temporary value and update twice.
>(example is shown is
>http://www.mail-archive.com/sqlite-users@sqlite.org/msg50894.html)
>but I don't want to use this workaround if possible
>because it is not gut feeling and less performance.
I also have been in the situation where a unique constraint gets raised
early: while inserting items in a nested tree (using integral LO-HI
intervals) you have to completely abandon unique constraints on the LO
and HI keys. Contrary to what occurs in other engines, SQLite seems to
assert constraints at insert/delete time, ignoring the fact that
(insert or delete) trigger will increment or decrement the upper part
of the tree interval on HI and LO keys (in the case of a nested tree).
At least, there should be a pragma or clause similar to the "deferred"
clause available for foreign keys, causing uniqueness/existence/custom
constraints to be checked only after FK (=trigger) action. With such
feature available, we wouldn't have to sacrifice integrity constraints
on critical columns.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
