Message info
 
To:General Discussion of SQLite Database From:Stephan Beal Subject:Re: [sqlite] Bug: Increment unique integer field Date:Mon, 7 May 2012 22:38:19 +0200
 

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" <kmedcalf@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