On Thu, Mar 8, 2012 at 1:52 PM, Pavel Ivanov <paiva...@gmail.com> wrote: >> So, please.. add INSERT OR UPDATE. I'd ask that INSERT OR REPLACE be >> modified to do an UPDATE to the record if the UNIQUE Constraint is violated >> instead of the delete, but that would break existing applications. >> >> Wasn't that the original intent of INSERT OR REPLACE?
BTW, you could use DEFERRED foreign key references to avoid some of the worst aspects of this issue. > No, because INSERT OR REPLACE can delete several rows and insert one > row instead of that. This kind of operation cannot be covered by > UPDATE in any way. When the conflict that leads to REPLACE is found via a non-primary key UNIQUE constraint violation then I agree, the REPLACE must be treated like DELETE-then-INSERT. Otherwise it should be treated as an UPDATE. *But*, of course, there may be multiple rows that could replace the same row, and the order in which the insertions are done may not be specified, so there's an ambiguity that should result in non-deterministic / undefined behavior (sometimes you get REPLACE as DELETE-then-INSERT, sometimes as UPDATE). This ambiguity can be resolved trivially if there are no non-primary key unique constraints, of course, but that's a very special case. And when I say "unique constraints" I mean "including unique indexes". There might be some odd interactions with any future feature that adds deferred unique constraints to worry about too. Though that actually would help resolve the ambiguity quite nicely if it applied only to non-primary key uniqueness constraint violations, though at the price of getting INSERT-then-DELETE in some cases instead of DELETE-then-INSERT. Very interesting problem... Nico -- _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
