Keith, this type of detailed response is why i rate this list so highly above most others. These are the threads i learn the most from :-D.
+1 Sorry for top-posting - writing on a phone. ----- stephan beal http://wanderinghorse.net/home/stephan/ http://gplus.to/sgbeal On May 7, 2012 4:44 PM, "Keith Medcalf" <kmedc...@dessus.ca> 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. > > > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
