On Mon, Sep 23, 2013 at 12:19 PM, Jean-Marie CUAZ <jm.c...@orens.fr> wrote:
> Hello,
>
> Thanks to SQlite developement team for this wonderfull library
>
> We have been hit today by the following :
>
> package require sqlite3
>
> sqlite3 db1 test
>
> db1 eval {CREATE TABLE T1 (A PRIMARY KEY, B, C, D UNIQUE DEFAULT '-')}
>
> db1 eval {INSERT INTO T1 VALUES (0, 1, 1, 'a') , (1, 1, 1, 'b') , (2, 1,
> 1 , 'c')}
>
> the issue is here :
>
> db1 transaction {
>
> db1 eval {INSERT OR REPLACE INTO T1 (A, B, C) VALUES (0, 2, 0),
> (1, 2, 0) , (2, 2, 0)}
>
> }
>
> -> no exception is raised to host langage Tcl for 2 rows not inserted
> because a UNIQUE constraint is not respected
> -> partial execution : 2 rows are definitely deleted from the table
> ...(ouch !)
>
>
Works as designed.
The first REPLACE changes the 0-row from 0,1,1,a into 0,2,0,-. The second
REPLACE replaces two rows 0,2,0,- and 1,1,1,b with a single new row
1,2,0,-. The 0,2,0,- was replace to satisfy the UNIQUE constraint and the
1,1,1,b was replaced to satisfy the PRIMARY KEY constraint. The third
REPLACE replaces the 0,2,0,- and 2,1,1,c rows with 2,2,0,-. Again, two
rows are replaced with one in order to satisfy two constraints: The UNIQUE
and the primary key. You are left with a single row: 2,2,0,-.
What exactly where you expecting to happen? What do you think REPLACE
should do if it has to content with two separate constraint conflicts on
two separate rows?
>
> Regards,
>
> Jean-Marie Cuaz
>
> ______________________________**_________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users<http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users>
>
--
D. Richard Hipp
d...@sqlite.org
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
