INSERT Statements and Data Entry

last_insert_rowid() is a built-in SQLite function that returns the ID of the most recently inserted row in your current database connection.

Programming

The Missing Piece After INSERT

When you insert a row into a table with an auto-incrementing primary key, the database assigns an ID to that row. The INSERT statement itself does not tell your application which ID was assigned. If the application needs that ID immediately, it must ask the database to return it.

In the real world

An application may need the new ID to link the inserted row to a related record, display the row to a user, construct a URL, or store the ID in an application variable.

Following the Database State

What happens between inserting a row, SQLite assigning its ID, calling last_insert_rowid(), and receiving that ID in the application?

The sequence is important. The database validates the data, finds the next available ID, writes the row, and updates its tracking of the most recently inserted ID for the current connection. A following call to last_insert_rowid() reads that tracked value and returns it.

Reading the Assigned ID

sql

SELECT last_insert_rowid();

SELECT last_insert_rowid();

Definition

last_insert_rowid() is a built-in SQLite function that returns the ID of the most recently inserted row in your current database connection.

1
Execute the INSERT statement.
2
Immediately execute SELECT last_insert_rowid(); using the same database connection.
3
Read the returned ID in the application.
4
Store the ID if it is needed for a later operation.

Worked example

Capturing a newly assigned ID

An application inserts a row and needs to use the new row's ID immediately.

1
Insert the row
The INSERT operation adds the row, and the database assigns its auto-generated primary-key ID.
2
Ask for the recent ID
The application executes SELECT last_insert_rowid(); immediately afterward on the same connection.
3
Capture the result
The application stores the returned ID in a variable so it can link related records, construct a URL, display the ID, or perform another operation.

Result

The application now has the ID assigned to the row it just inserted.

Connection-Local Results

Each database connection maintains its own record of the most recently inserted row ID. Therefore, last_insert_rowid() does not return the most recent ID from every user or every connection. It returns the most recent ID tracked for the connection that executes the function.

If two database connections insert rows at nearly the same time, which inserted ID does last_insert_rowid() return for each connection?

In the real world

If User A inserts a row and receives ID 100 while User B inserts a row and receives ID 101, last_insert_rowid() in User A's connection returns 100, not 101. The function in User B's connection returns 101.

Passing the ID Forward

How does the generated ID identify the new row and connect it to later data or an application variable?

The returned ID becomes useful only when the application captures it. Once stored in an application variable, the ID can be passed to a later operation, used to link related records, included in a URL, or displayed to a user.

sql

INSERT statement
SELECT last_insert_rowid();
store the returned ID in an application variable

INSERT statement
SELECT last_insert_rowid();
store the returned ID in an application variable

The exact application-language syntax for storing the result can vary, but the database sequence remains the same: insert, retrieve immediately on the same connection, and capture the returned ID before using it elsewhere.

Mistakes During ID Retrieval

Common mistake
Assuming the INSERT statement itself tells the application the assigned ID.
What it looks like
The application performs an INSERT but never executes SELECT last_insert_rowid();.
Why it fails
The INSERT statement does not tell the application what auto-generated ID was assigned.
Do this instead
```
Execute SELECT last_insert_rowid(); immediately after the INSERT.
```
Common mistake
Retrieving the ID from a different database connection.
What it looks like
The row is inserted through one connection, but last_insert_rowid() is called through another.
Why it fails
The function is connection-local and each connection maintains its own most recently inserted row ID.
Do this instead
Call the function using the same connection that performed the INSERT.
Common mistake
Failing to capture the returned ID in application code.
What it looks like
The application runs the SELECT successfully but does not store its result.
Why it fails
The application cannot use the ID later to link records, construct a URL, display it, or perform another operation.
Do this instead
Read the result and store it in an application variable immediately.
Common mistake
Treating another user's inserted ID as the current connection's result.
What it looks like
User A expects last_insert_rowid() to return User B's newer ID.
Why it fails
Each connection has an independent record.
Do this instead
Use the value returned in the connection that performed the relevant INSERT.

Check Your Sequence

EASY

An application inserts a row, needs its assigned ID for a related operation, and has not yet closed or changed its database connection. What should it do next, and what should it do with the returned value?

Hints

Use the SQLite function that reports the most recently inserted row ID.
Run it immediately after the INSERT on the same connection.
Store the returned ID in application code.

Your attempt (not saved)

What do you think happens?

Connection A inserted a row with ID 100. Connection B then inserted a row with ID 101. What does last_insert_rowid() return when called on Connection A?

100
101
Both IDs
No ID

Reveal answer

Answer:

100

The function is connection-local. Connection A retains its own most recently inserted ID, even when another connection inserts a newer row.

Key Takeaways

1
An INSERT can create a row with an automatically assigned primary-key ID without telling the application which ID was assigned.
2
Use SELECT last_insert_rowid(); to retrieve the most recently inserted row ID.
3
Run the retrieval immediately after the INSERT using the same database connection.
4
The function is connection-local, so simultaneous inserts through different connections do not replace one another's tracked IDs.
5
Capture the returned ID in application code when it must be used for related records, URLs, display, or later operations.

At a glance

An auto-generated ID must be retrieved separately when an application needs to use it after an INSERT.
SQLite provides last_insert_rowid(), called with SELECT last_insert_rowid();.
The function reports the most recently inserted row ID for the current database connection.
Immediate retrieval and application-side storage make the ID available for linking, URLs, display, and subsequent operations.

More concepts starting with I