thank you for valuable comments.
> 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.
I hope the feature too.
Could sqlite developers plan to implement it?
2010/4/19 Jean-Christophe Deschamps <j...@q-e-d.org>:
>
>>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).
>
>
> _______________________________________________
> 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
