[ACCEPTED]-Does SQLite support SCOPE_IDENTITY?-sqlite

Accepted answer
Score: 27

If you're not using the C interface for 2 programming and want to do the process from 1 an SQL Command try: SELECT last_insert_rowid()


Score: 23

Check out the FAQ. The sqlite3_last_insert_rowid() function will do it. Careful 1 of triggers though.

Score: 1

The last_insert_rowid() results in the row id from the very 26 LAST insert into ANY table. Definitely not 25 thread-safe as mentioned in other answers.

If 24 you absolutely need to make sure that you are 23 getting the correct row id returned, regardless 22 of threads, async etc (for example, if you 21 intend to use the rowid as a foreign key 20 in another table), here's a way:

  1. insert a text column into the desired table (this will hold a GUID)
  2. manually generate a GUID (using whatever libraries are available in your language) and hold it in memory
  3. insert your data into the table along with the GUID you just generated
  4. retrieve the (supposed) rowid via last_insert_rowid()
  5. retrieve the row (or just the GUID) from your table using this rowid
  6. compare the GUID from the retrieved row with the GUID you still have in memory
    • if they are the same, happy days, you have the correct rowid
    • if they are different, you need to query the table for a match to the GUID you have in memory for the correct rowid

Obviously 19 this solution has considerable performance 18 drawbacks and you need to make a judgement 17 call on whether the risks of mismatched 16 data outweigh the performance toll. In my 15 case, I needed to be certain of the integrity 14 of my data above all else so it was fine 13 for performance to take the hit. (Just how 12 big a hit, I can't say.)

You might get the 11 urge to skip the 4th and 5th steps and get 10 the rowid using your GUID; you might also 9 get the urge to just use the GUID as the 8 primary identifier and foreign key. After 7 all these would make the code simpler and mean 6 less columns in your table... RESIST THAT 5 URGE. Integers as primary identifiers are 4 easily indexable which makes them faster/more 3 efficient in WHERE clauses and JOINS; a 2 GUID or string just doesn't index as well 1 as an integer.

More Related questions