On Tue, Jul 12, 2016 at 2:12 PM, Dominique Devienne <ddevie...@gmail.com>
wrote:
> C:\Users\ddevienne>sqlite3
> SQLite version 3.10.2 2016-01-20 15:27:19
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> create table t (id integer primary key autoincrement, name text
> unique);
> sqlite> insert into t values (1, 'one'), (2, 'two');
> sqlite> select * from t;
> 1|one
> 2|two
> sqlite> insert into t values (1, 'one bis');
> Error: UNIQUE constraint failed: t.id
> sqlite> insert or replace into t values (1, 'one bis');
> sqlite> select * from t;
> 1|one bis
> 2|two
> sqlite> insert or replace into t (name) values ('one bis');
> sqlite> select * from t;
> 2|two
> 3|one bis
>
> 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.
>
> 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.
>
> Is that normal or expected?
> Is there a SQL way (one statement ideally) to achieve what we'd like?
>
sqlite> insert or replace into t values (3, 'two');
sqlite> select * from t;
3|two
Worse, and that's even more unexpected to me, I tried doing the insert or
replace above,
but specifying the PK the time, *and* explicitly trying to conflict on the
UNIQUE index,
and to me great surprise (horror?), that actually deleted one row instead
of failing!
That an insert or replace can result in fewer rows (i.e. akin to a delete)
is shocking to me.
Can someone please shed some light on this too? Thanks again, --DD
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
