Search code examples
sqliteuniqueidentityauto-generate

Sqlite insert into with unique names, getting id

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.

  1. If i use replace, their id (INTEGER PRIMARY KEY) updates which breaks my db (entries point to nonexistent IDs)
  2. 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?

Solution

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