On 2016/07/12 2:12 PM, Dominique Devienne wrote:
In the session above, we can see that an insert or replace w/o an id (the PK) value, results in the id changing in the table, which is not what we'd like.
If you ask any DB engine to INSERT, and you do not specify the autoinc/pk/identity ID, then a new ID will always be used. By specifying "OR REPLACE" in SQLite it /replaces/ the offending row by deleting it and inserting the new version of the row as you asked (it never overwrites /some/ values in the row) - however, if you fail to specify the ID for the row, then any newly inserted row (like any other inserted row) MUST get a new row ID.
The (incorrect on our part) thinking was that the UNIQUE index would also be used to recognize that the row already existed, and the id to remain the same (In reality in this case the natural key checked by the UNIQUE index is passed in unchanged to the insert or replace, and it's other columns that change), but apparently the autoincrement is first applied, and then the UNIQUE index is apparently still used, resulting in this unwanted ID change.
The best way to think about this or remember it, is that SQLite (or any DB engine I think) will never adjust the data within a row unless asked to UPDATE. When you ask it to INSERT or REPLACE or any other such a thing, an entire row will always be affected and handled like a new row should you omit any fields. (This is usually documented and expected, even by the standard).
You kind-of expected the INSERT OR REPLACE to be more of an INSERT OR UPDATE (which is the commonly-referred to UPSERT operation) and not really supported natively by many - unless you count MERGE (which does quite a bit more).
Interestingly, as is evident from this list - This assumption is extremely common and happens all the time, even if it gets discussed here no end... (just search the list for "UPSERT"), so don't feel alone, near everyone has this wrong initially.
_______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
