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 bisIn 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? Thanks, --DD _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
