On May 7, 2012, at 11:57 AM, John Yani wrote: > In an SQL statement that inserts, deletes, or updates many rows, > SQLite as well as MySQL InnoDB checks UNIQUE and FOREIGN KEY > constraints row-by-row. > According to the SQL standard, the default behavior should be deferred > checking.
Right, SQLite doesn't support DEFERRABLE INITIALLY DEFERRED for unique constraints, only for foreign constraints. Very sad :/ But wait! Perhaps using a 'on conflict' clause could save the day? Lets try: create table numbers(num int unique ); insert into numbers( num ) values( 1 ); insert into numbers( num ) values( 2 ); insert into numbers( num ) values( 3 ); Ok, got 3 rows, numbered 1 to 3. sqlite> select * from numbers; num 1 2 3 First, lets try a regular update: sqlite> update numbers set num=num+1; Error: column num is not unique Argh… that missing deferred unique key… ok… lets try with a conflict clause: update or replace numbers set num=num+1; Look like it went through… let check… sqlite> select * from numbers; num 2 4 Oh, no!!!!… Lost one row during the update!!! sqlite> select count( * ) from numbers; count( * ) 2 Arghhhhhh!!!! $ sqlite3 -version 3.7.11 2012-03-20 11:35:50 00bb9c9ce4f465e6ac321ced2a9d0062dc364669 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
