On 21 Jan 2017, at 4:32pm, James K. Lowden <jklow...@schemamania.org> wrote:
> Cecil Westerhof <cldwester...@gmail.com> wrote: > >> I would think that this would work: >> UPDATE desktops >> SET indexNo = indexNo + 1 >> >> But it does not, it gives: >> Error: UNIQUE constraint failed: desktops.indexNo > > It should work. It does work in other DBMSs, but it doesn't in > SQLite. It is a failure of atomicity in SQLite semantics. This is one I do feel is a bug in SQLite. The command >> UPDATE desktops SET indexNo = indexNo + 1 can lead to violations of the UNIQUE constraint but whether it does or not is an implementation detail (depends which order the rows are processed) and not under user-control. So the proper requirement is that the UNIQUE check be made at the end of the transaction. And at the end of the transaction there would be no violations, no matter in which order the SQL engine chose to process rows. Unfortunately changing SQLite to check the constraints at the end of the transaction rather than as each change is made would require a lot of programming. Maybe it’s one for SQLite4. I think I’ve seen other SQL implementations where you can state at which point the constraints are enforced. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
