Automatic Primary Keys and AUTOINCREMENT
last_insert_rowid() is a built-in SQLite function that returns the ID of the most recently inserted row in your current database connection.
The Missing Link After INSERT
When you insert a row into a table with an auto-incrementing primary key, the database automatically assigns an ID to that row. The INSERT operation writes the row, but the statement alone does not tell your application which ID was assigned. If the application needs that ID immediately, it must ask the database to reveal it.
The Insert-Then-Retrieve Sequence
The reliable sequence is simple: perform the INSERT, then immediately execute SELECT last_insert_rowid(); in the same database connection. During the INSERT, the database validates the data, finds the next available ID, writes the row, and updates its record of the most recently inserted ID for that connection. The SELECT then reads that recorded value.
Retrieving a Newly Assigned ID
A database inserts a new row and automatically assigns it an ID. How should the application retrieve that ID for immediate use?
Insert the row: Execute the INSERT operation. The database validates the data, finds the next available ID, writes the row, and records the newly inserted row ID for the current connection.
Ask for the ID: Immediately execute SELECT last_insert_rowid(); using the same database connection.
Capture the result: Store the returned ID in application code so it can be used by a later operation.
The application now has the ID assigned to the row it just inserted and can use it to link related records, construct a URL, display the value, or perform another operation.
Using the Retrieved Value
Retrieving the ID is useful because the application can carry that value beyond the INSERT operation. It can store the value in a variable, use it to link the new row to related records, include it in a URL, display it to a user, or use it in another database operation. The important pattern is that the database supplies the ID and the application captures it before continuing.
Suppose an application creates a new record and then needs to link another record to it. The application first inserts the new record, retrieves its ID with SELECT last_insert_rowid();, and stores the returned value. The stored ID can then be supplied to the operation that creates the related record.
Connection-Local Results
last_insert_rowid() is a built-in SQLite function that returns the ID of the most recently inserted row in your current database connection.
Each database connection maintains its own record of the most recently inserted row ID. Therefore, simultaneous inserts by different users do not interfere with one another's ID retrieval. If User A inserts a row and receives ID 100 while User B inserts a row and receives ID 101, calling last_insert_rowid() through User A's connection returns 100, not 101. The function does not return the latest ID across every connection; it returns the latest ID tracked by the connection making the call.
| Caller | Connection | Inserted row ID | Result from last_insert_rowid() |
|---|---|---|---|
| User A | Connection A | 100 | 100 |
| User B | Connection B | 101 | 101 |
Each connection retrieves its own most recently inserted row ID.
Automatic Assignment Before and After
Before insertion, the application has the row's data but does not yet have the automatically assigned primary-key value. After the database inserts the row, it has assigned an ID and updated the current connection's record of the most recently inserted row ID. Calling last_insert_rowid() reveals that value to the application.
Mistakes to Avoid
Assuming the INSERT statement already tells the application the assigned ID.
The INSERT operation automatically assigns the ID, but the INSERT statement alone does not reveal which ID was assigned.
Fix:
Immediately retrieve the value with SELECT last_insert_rowid(); in the same database connection.Treating last_insert_rowid() as a database-wide latest-ID function.
The function is connection-local. Each connection tracks its own most recently inserted row ID.
Fix:
Call the function through the connection that performed the INSERT whose ID you need.Retrieving the ID through a different connection.
The function reads the tracking value belonging to the connection making the SELECT call.
Fix:
Keep the INSERT and the immediate ID retrieval on the same connection.Failing to capture the returned ID in application code.
The application then lacks the value needed to link related records, construct URLs, display the ID, or perform a subsequent operation.
Fix:
Store the returned ID in an application variable or equivalent application-level value.
Practice the Workflow
What do you think happens?
User A inserts a row and receives ID 100. User B inserts another row and receives ID 101 through a different connection. What will last_insert_rowid() return when User A calls it through User A's connection?
Reveal answer
Answer: 100
last_insert_rowid() is connection-local. User A's connection tracks User A's most recently inserted row ID, even though User B inserted another row through a different connection.
Describe the correct sequence for an application that inserts a row, needs the new row's automatically assigned ID, and then wants to use that ID to link a related record.
Hints
- Start with the INSERT operation.
- Retrieve the ID with SELECT last_insert_rowid(); immediately afterward.
- Make sure both operations use the same database connection.
- Store the returned ID before performing the related operation.
Key Takeaways
- An automatically assigned primary-key ID is not revealed by the INSERT statement alone.
- Use SELECT last_insert_rowid(); immediately after the INSERT to retrieve the newly inserted row ID.
- The function returns the most recently inserted row ID for the current database connection, not a database-wide value.
- Different connections can retrieve their own correct IDs without interfering with one another.
- Capture the returned ID in application code when it must be used for related records, URLs, display, or later operations.
Key Takeaways
- An INSERT can create a row and automatically assign its primary-key ID without telling the application what that ID is.
- SELECT last_insert_rowid(); retrieves the most recently inserted row ID for the current SQLite connection.
- Connection-local tracking prevents concurrent inserts through different connections from interfering with each other's ID retrieval.
- Applications should capture the returned ID immediately when they need to link records, construct URLs, display the value, or perform another operation.