> 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. > http://stackoverflow.com/questions/7703196/sqlite-increment-unique-integer- > field
The behaviour is correct. Consider the case where num were the referenced in a foreign key constraint and updates were cascading, any method of achieving the update other than updating through a cursor (including allowing deferral of integrity checking until commit time) which violates the unique constraint in an interim update would result in an inconsistent database (loss of integrity), for example, if a row with num = n+1 were updated following the update of a row with num = n, for any n. There are ways to "re-phrase" the update to avoid this, but it requires domain knowledge and therefore the engine cannot do it for you. Nor should it. Create table numbers (num int unique); Create table others (a int, num int unique references numbers (num) on update cascade); Insert into numbers values (1), (2), (3), (4); Insert into others values (1,1), (2,2), (3,3), (4,4); Then any interim update to num which violated the unique constraint would result in integrity loss and the update should fail irrespective of row order. And you ought not depend on row order unless explicitly stated in the SQL statement. Turning off constraint checking (or deferring it until commit time) places the consequence of "not knowing what you are doing" directly in the hands of the programmer (where it belongs). If you know enough about the database to disable the integrity checking, you ought to be struck with the consequences (if any). The only way to do the update without such side-effects is to ensure that the update does not have any interim (row by row) violations of integrity. In other engines you would use: update numbers set num = num + 1 from numbers order by num desc; To control the row processing order by performing the update though "current of cursor", which would always obtains the correct result every time. Perhaps a useful enhancement would be to permit an update to use from and order by clauses thus allowing such an update to be expressed directly. Effectively, such an update would become a "select" where instead of returning rows, the return a row would be replaced with the update operation... Some refer to this as an updateable view. It really isn't. It is still an update of "current of cursor", the update just takes place for each valid result row in a result set by allowing additional tables to be joined into the cursor, rather than restricting the cursor to only the single updated table. You can achieve this presently by creating a view with the correct ordering, and then an update trigger for the num column on the view which updates the underlying table, then performing the update against the view: Create table numbers (num int unique); Create table others (a int, num int unique references numbers (num) on update cascade); Insert into numbers values (1), (2), (3), (4); Insert into others values (1,1), (2,2), (3,3), (4,4); Create view updatenumbers as select num from numbers order by num desc; Create trigger updnum instead of update of num on updatenumbers begin update numbers set num = new.num where num=old.num; end; update updatenumbers set num = num + 1; sqlite> select * from numbers; select * from others; 2 3 4 5 1|2 2|3 3|4 4|5 --- () ascii ribbon campaign against html e-mail /\ www.asciiribbon.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
