Sqlite embed with unique names, get identifier

Question

I have a list of lines to insert in db. They MUST be unique. When I insert, I would like their ID (use as a foreign key in another table), so I use last_insert_rowid. I get 2 problems.

• If I use replacement, their identifier (INTEGER PRIMARY KEY), which my db breaks (position indicates non-existent identifiers)
• If I use ignore, rowid is not updated, so I do not get the correct identifier

How to get identifiers? if I do not need it, I would not want to use the select statement to check and insert a row if it does not exist. How can I do it?

Answer 1

I use below

insert into tbl(c_name) select 'val' where not exists(select id from tbl where c_name ='val');
select id from tbl where c_name ='val';

Answer 2

If a UNIQUE constraint violation occurs, the REPLACE algorithm deletes existing rows that cause the violation of constraints before inserting or updating the current row, and the command continues to execute normally. This causes rowid to change and creates the following problem.

Y:> **sqlite3 test**  
SQLite version 3.7.4  
Enter ".help" for instructions  
Enter SQL statements terminated with a ";"  
sqlite> **create table b (c1 integer primary key, c2 text UNIQUE);**  
sqlite> **insert or replace into b values (null,'test-1');**  
sqlite> **select last_insert_rowid();**  
1  
sqlite> **insert or replace into b values (null,'test-2');**  
sqlite> **select last_insert_rowid();**  
2  
sqlite> **insert or replace into b values (null,'test-1');**  
sqlite> **select last_insert_rowid();**  
3  
sqlite> **select * from b;**  
2|test-2  
3|test-1  

The work around is to change the definition of column c2 as follows

create table b (c1 integer primary key, c2 text UNIQUE ON CONFLICT IGNORE);

and remove the "or replace" sentence from your inserts;

then when testing after your insert, you will need to execute the following sql: select last_insert_rowid(), changes();

sqlite> **create table b (c1 integer primary key, c2 text UNIQUE ON CONFLICT IGNORE);**  
sqlite> **insert into b values (null,'test-1');**  
sqlite> **select last_insert_rowid(), changes();**  
1|1  
sqlite> **insert into b values (null,'test-2');**  
sqlite> **select last_insert_rowid(), changes();**  
2|1  
sqlite> **insert into b values (null,'test-1');**  
sqlite> **select last_insert_rowid(), changes();**  
2|0  

, rowid "test-1", . , , .

Answer 3

" ", , , , , ? , , 1 2 - , , , . , SQL, , ?...

: , , SQL ! , :

CREATE TABLE Foo(
  theid INTEGER PRIMARY KEY AUTOINCREMENT,
  aword TEXT UNIQUE ABORT
  )

then any attempt to INSERT a duplicate word will fail (the keyword ABORTis optional, because it is the default for UNIQUE) - this is not what you need if you say the words “MUST be unique”, ie it is an error if they are not?