[ACCEPTED]-Predict next auto-inserted row id (SQLite)-sqlite

Accepted answer
Score: 28

Try SELECT * FROM SQLITE_SEQUENCE WHERE name='TABLE';. This will contain a field called 4 seq which is the largest number for the selected 3 table. Add 1 to this value to get the next 2 ID.

Also see the SQLite Autoincrement article, which is where the above 1 info came from.


Score: 16

Either scrapping or committing a series 11 of database operations all at once is exactly 10 what transactions are for. Query BEGIN; before 9 the user starts fiddling and COMMIT; once he/she's 8 done. You're guaranteed that either all 7 the changes are applied (if you commit) or 6 everything is scrapped (if you query ROLLBACK;, if 5 the program crashes, power goes out, etc). Once 4 you read from the db, you're also guaranteed 3 that the data is good until the end of the 2 transaction, so you can grab MAX(id) or whatever 1 you want without worrying about race conditions.


Score: 3

You can probably get away with adding 1 12 to the value returned by sqlite3_last_insert_rowid under certain 11 conditions, for example, using the same 10 database connection and there are no other 9 concurrent writers. Of course, you may refer 8 to the sqlite source code to back up these 7 assumptions.

However, you might also seriously 6 consider using a different approach that 5 doesn't require predicting the next ID. Even 4 if you get it right for the version of sqlite 3 you're using, things could change in the 2 future and it will certainly make moving 1 to a different database more difficult.

Score: 2

Insert the row with an INVALID flag of some 8 kind, Get the ID, edit it, as needed, delete 7 if necessary or mark as valid. That and 6 don't worry about gaps in the sequence

BTW, you 5 will need to figure out how to do the invalid 4 part yourself. Marking something as NULL 3 might work depending on the specifics.

Edit: If 2 you can, use Eevee's suggestion of using 1 proper transactions. It's a lot less work.

Score: 2

I realize your application using SQLite 39 is small and SQLite has its own semantics. Other 38 solutions posted here may well have the 37 effect that you want in this specific setting, but 36 in my view every single one of them I have 35 read so far is fundamentally incorrect and 34 should be avoided.

In a normal environment 33 holding a transaction for user input should 32 be avoided at all costs. The way to handle 31 this, if you need to store intermediate 30 data, is to write the information to a scratch 29 table for this purpose and then attempt 28 to write all of the information in an atomic 27 transaction. Holding transactions invites 26 deadlocks and concurrency nightmares in 25 a multi-user environment.

In most environments 24 you cannot assume data retrieved via SELECT 23 within a transaction is repeatable. For 22 example

SELECT Balance FROM Bank ...
UPDATE Bank SET Balance = valuefromselect + 1.00 WHERE ...

Subsequent to UPDATE the value of 21 balance may well be changed. Sometimes 20 you can get around this by updating the 19 row(s) your interested in Bank first within 18 a transaction as this is guaranteed to lock 17 the row preventing further updates from 16 changing its value until your transaction 15 has completed.

However, sometimes a better 14 way to ensure consistency in this case is 13 to check your assumptions about the contents 12 of the data in the WHERE clause of the update 11 and check row count in the application. In 10 the example above when you "UPDATE Bank" the 9 WHERE clause should provide the expected 8 current value of balance:

WHERE Balance = valuefromselect

If the expected 7 balance no longer matches neither does the 6 WHERE condition -- UPDATE does nothing and 5 rowcount returns 0. This tells you there 4 was a concurrency issue and you need to 3 rerun the operation again when something 2 else isn't trying to change your data at 1 the same time.

Score: 2
select max(id) from particular_table is unreliable for the reason below..


"The normal ROWID selection algorithm 10 described above will generate monotonically 9 increasing unique ROWIDs as long as you 8 never use the maximum ROWID value and you 7 never delete the entry in the table with 6 the largest ROWID. If you ever delete rows 5 or if you ever create a row with the maximum 4 possible ROWID, then ROWIDs from previously 3 deleted rows might be reused when creating 2 new rows and newly created ROWIDs might 1 not be in strictly ascending order."

Score: 0

I think this can't be done because there 5 is no way to be sure that nothing will get 4 inserted between you asking and you inserting. (you 3 might be able to lock the table to inserts 2 but Yuck)

BTW I've only used MySQL but I 1 don't think that will make any difference)

Score: 0

Most likely you should be able to +1 the 9 most recent id. I would look at all (going 8 back a while) of the existing id's in the 7 ordered table .. Are they consistent and 6 is each row's ID is one more than the last? If 5 so, you'll probably be fine. I'd leave a 4 comments in the code explaining the assumption 3 however. Doing a Lock will help guarantee 2 that you're not getting additional rows 1 while you do this as well.

Score: 0

Select the last_insert_rowid() value.


Score: 0

Most of everything that needs to be said 5 in this topic already has... However, be very careful of race conditions when doing 4 this. If two people both open your application/webpage/whatever, and 3 one of them adds a row, the other user will 2 try to insert a row with the same ID and 1 you will have lots of issues.

Score: 0
select max(id) from particular_table;

The next id will be +1 from the maximum 1 id.

More Related questions