Sqlite insert into with unique names, getting id

Question

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

If i use replace, their id (INTEGER PRIMARY KEY) updates which breaks my db (entries point to nonexistent IDs)
If i use ignore, rowid is not updated so i do not get the correct ID

How do i get their Ids? if i dont need to i wouldnt want to use a select statement to check and insert the string if it doesnt exist . How should i do this?

score 10 · Answer 1 · edited May 09 '23 at 00:23

When a UNIQUE constraint violation occurs, the REPLACE algorithm deletes pre-existing rows that are causing the constraint violation prior to inserting or updating the current row and the command continues executing normally. This causes the 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 the c2 column as follows:

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

and to remove the or replace clause from your inserts;

then when test 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

The return value of changes after the 3rd insert will be a notification to your application that you will need to lookup the rowid of "test-1", since it was already on file. Of course if this is a multi-user system, you will need to wrap all this in a transaction as well.

With `insert or replace into b values (1,'test-1');` i cant know about (1,val) so i used null instead. I get the same results if i try `insert or replace into b (c2) values(val)` and `insert or replace into b (c2) select val` with the same results. Here is an image (why cant i copy/paste into/from sqlite-shell!) http://i.imgur.com/2a6IY.png — , Jan 27 '11 at 21:43
hmm, so i basically check changes and if its 1 i use the rowid, otherwise i need a select statement. It sounds a lot like my answer below. Ok this is an acceptable solution, +1. I'll the current answer as accept because i tested it under mysql with success. — , Jan 28 '11 at 19:16
you do know that that "accepted" answer does not actually answer your question about eliminating the select statement when it is not needed; — Noah, Jan 31 '11 at 17:57
I'm afraid your answer helps but does not answer the question as well. OP wants to avoid additional select queries to get the id, you only provide a way to avoid this when the unique constraint does **not** fail. See also my answer https://stackoverflow.com/a/65869514/7015849 — spawn, Jan 24 '21 at 10:42

score 3 · Accepted Answer · answered Jan 27 '11 at 21:50

3

I use the below currently

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';

answered Jan 27 '11 at 21:50

Alex Martelli · Answer 3 · 2009-05-10T14:48:34.610

By "they MUST be unique", do they mean you are sure that they are, or that you want an error as a result if they aren't? If you just make the string itself a key in its table, then I don't understand how either 1 or 2 could be a problem -- you'll get an error as desired in case of unwanted duplication, otherwise the correct ID. Maybe you can clarify your question with a small example of SQL code you're using, the table in question, what behavior you are observing, and what behavior you'd want instead...?

Edited: thanks for the edit but it's still unclear to me what SQL is giving you what problems! If your table comes from, e.g.:

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

then any attempt to INSERT a duplicated word will fail (the ABORT keyword is optional, as it's the default for UNIQUE) -- isn't that what you want given that you say the words "MUST be unique", i.e., it's an error if they aren't?

instead of an error i would like last_insert_rowid to give me the id (PK) of the text. So far replace changes the ID which breaks previous entries and an error doesnt return the rowid. It sounds like i do need to do this in two steps? a select statement then a insert statement if it doesnt exist — , May 10 '09 at 21:01

score 0 · Answer 4 · answered Jan 24 '21 at 10:34

The correct answer to your question is: This cannot be done in sqlite. You have to make an additional select query. Quoting the docs for last_insert_rowid:

An INSERT that fails due to a constraint violation is not a successful INSERT and does not change the value returned by this routine. Thus INSERT OR FAIL, INSERT OR IGNORE, INSERT OR ROLLBACK, and INSERT OR ABORT make no changes to the return value of this routine when their insertion fails

Mat · Answer 5 · 2022-02-05T13:14:49.273

Having the same problem in 2022, but since SQLite3 version 3.35.0 (2021-03-12), we have RETURNING. Combined with UPSERT, it is now possible to achieve this

sqlite> create table test (id INTEGER PRIMARY KEY, text TEXT UNIQUE);
sqlite> insert into test(text) values("a") on conflict do update set id = id returning id;
1
sqlite> insert into test(text) values("a") on conflict do update set id = id returning id;
1
sqlite> insert into test(text) values("b") on conflict do update set id = id returning id;
2
sqlite> insert into test(text) values("b") on conflict do update set id = id returning id;
2
sqlite> select * from test;
1|a
2|b
sqlite> insert into test(text) values("b") on conflict do nothing returning id;
sqlite>

Sadly, this is still a workaround rather than an elegant solution... On conflict, the insert becomes an update. This means that your update triggers will fire, so you may want to stay away from this!

When the insert is converted into an update, it needs to do something (cf link). However, we don't want to do anything, so we do a no-op by updating id with itself. Then, returning id gives us the what we want.

Notes:

Our no-op actually does an update, so it costs time, and the trigger on update will fire. But without triggers, it has no effect on the data
Using on conflict do nothing returning id does not fail, but does not return the id.
If usable (again, check your triggers), and if all your tables use the primary key id, then this technique does not need any specialization: just copy/paste on conflict do update set id = id returning id;

Sqlite insert into with unique names, getting id

5 Answers5