javascript python java c# php android html jquery c++ css ios mysql sql r node.js arrays reactjs c asp.net json ruby-on-rails .net sql-server swift python-3.x objective-c django angular angularjs excel regex pandas ruby iphone ajax linux xml vba asp.net-mvc spring laravel database wordpress typescript string wpf mongodb windows xcode postgresql bash oracle git vb.net amazon-web-services multithreading list firebase flutter eclipse spring-boot dataframe azure react-native algorithm docker macos forms image visual-studio scala powershell function twitter-bootstrap numpy api performance selenium winforms python-2.7 matlab vue.js sqlite apache hibernate entity-framework loops rest shell facebook express android-studio csv linq maven qt swing unit-testing file tensorflow

[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()

http://www.sqlite.org/lang_corefunc.html

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.

Source: stackoverflow.com

More Related questions

How to insert duplicate rows in SQLite with a unique ID?

Sqlite using command line

SQLite: Should LIKE 'searchstr%' use an index?

simple query: not implemented by SQLite JDBC driver

android sqlite CREATE TABLE IF NOT EXISTS

Cross-table UPDATE in SQLITE3

SQLite Update Syntax for string concatenation?

SQL combining GROUP BY and SUM

sqlite3 is chopping/cutting/truncating my text columns

Reset the row number count in SQLite3/MySQL