Does SQLite support SCOPE_IDENTITY?
The last_insert_rowid() results in the row id from the very LAST insert into ANY table. Definitely not thread-safe as mentioned in other answers.
If you absolutely need to make sure that you are getting the correct row id returned, regardless of threads, async etc (for example, if you intend to use the rowid as a foreign key in another table), here's a way:
- insert a text column into the desired table (this will hold a GUID)
- manually generate a GUID (using whatever libraries are available in your language) and hold it in memory
- insert your data into the table along with the GUID you just generated
- retrieve the (supposed) rowid via
last_insert_rowid() - retrieve the row (or just the GUID) from your table using this rowid
- 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 this solution has considerable performance drawbacks and you need to make a judgement call on whether the risks of mismatched data outweigh the performance toll. In my case, I needed to be certain of the integrity of my data above all else so it was fine for performance to take the hit. (Just how big a hit, I can't say.)
You might get the urge to skip the 4th and 5th steps and get the rowid using your GUID; you might also get the urge to just use the GUID as the primary identifier and foreign key. After all these would make the code simpler and mean less columns in your table... RESIST THAT URGE. Integers as primary identifiers are easily indexable which makes them faster/more efficient in WHERE clauses and JOINS; a GUID or string just doesn't index as well as an integer.
Check out the FAQ. The sqlite3_last_insert_rowid() function will do it. Careful of triggers though.
If you're not using the C interface for programming and want to do the process from an SQL Command try: SELECT last_insert_rowid()
http://www.sqlite.org/lang_corefunc.html