Automatic Primary Keys and AUTOINCREMENT
When you insert a row into a table with an auto-incrementing primary key, the database automatically assigns an ID to that row.
Try it: Keys and Constraints
How SQLite enforces PRIMARY KEY, UNIQUE, NOT NULL and FOREIGN KEY constraints on INSERT and DELETE, how an INTEGER PRIMARY KEY assigns ids (with and without AUTOINCREMENT), what INSERT OR IGNORE skips, and what ON DELETE RESTRICT, CASCADE and SET NULL do to child rows.
How it works
- INSERT: a NULL id is replaced by max(id) + 1 — or, with AUTOINCREMENT, by one more than the largest id ever used — and last_insert_rowid() reports it.
- Constraints are checked in order: NOT NULL, then the PRIMARY KEY, then UNIQUE, then the FOREIGN KEY (the parent row must exist). The first failure aborts the statement with SQLite's error message.
- INSERT OR IGNORE silently skips a row that breaks NOT NULL, PRIMARY KEY or UNIQUE — but a foreign-key failure is still an error.
- DELETE of a parent row that has children: RESTRICT refuses, CASCADE deletes the children too, SET NULL clears their foreign key. None of this happens unless PRAGMA foreign_keys = ON (SQLite's default is OFF).
Default run (5 steps): 3 artists and 3 albums. PRAGMA foreign_keys = ON, ON DELETE RESTRICT. Choose an operation. Example: INSERT INTO artist (name) VALUES ('Queen'). … Error: UNIQUE constraint failed: artist.name. Nothing was changed (the statement is rolled back).
Simplified: A toy relational engine in the browser, not SQLite itself: a small structured query model built from the controls (no SQL text is parsed or executed), typed INTEGER/TEXT/NULL values, and tables of at most a few rows. Results were checked against Python's sqlite3 (SQLite 3.49). Two fixed tables of at most 8 rows and ids up to 99; changing the schema recreates the tables with the starting rows. Error texts and ids were checked step by step against sqlite3.
Loading the simulation…
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?
- 1Insert 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.
- 2Ask for the ID
Immediately execute SELECT
last_insert_rowid(); using the same database connection. - 3Capture 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
Common mistake Assuming the INSERT statement already tells the application the assigned ID.
What it looks likeThe row is inserted, but the application continues without executing SELECT last_insert_rowid();.
Why it failsThe INSERT operation automatically assigns the ID, but the INSERT statement alone does not reveal which ID was assigned.
Do this insteadImmediately retrieve the value with SELECT last_insert_rowid(); in the same database connection.
Common mistake Treating last_insert_rowid() as a database-wide latest-ID function.
What it looks likeUser A expects the function to return User B's most recently inserted ID.
Why it failsThe function is connection-local. Each connection tracks its own most recently inserted row ID.
Do this insteadCall the function through the connection that performed the INSERT whose ID you need.
Common mistake Retrieving the ID through a different connection.
What it looks likeThe INSERT runs through one connection, but SELECT last_insert_rowid(); runs through another.
Why it failsThe function reads the tracking value belonging to the connection making the SELECT call.
Do this insteadKeep the INSERT and the immediate ID retrieval on the same connection.
Common mistake Failing to capture the returned ID in application code.
What it looks likeThe application retrieves the ID but does not store it before attempting a related operation.
Why it failsThe application then lacks the value needed to link related records, construct URLs, display the ID, or perform a subsequent operation.
Do this insteadStore 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
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
- 1An automatically assigned primary-key ID is not revealed by the INSERT statement alone.
- 2Use SELECT
last_insert_rowid(); immediately after the INSERT to retrieve the newly inserted row ID. - 3The function returns the most recently inserted row ID for the current database connection, not a database-wide value.
- 4Different connections can retrieve their own correct IDs without interfering with one another.
- 5Capture the returned ID in application code when it must be used for related records, URLs, display, or later operations.
At a glance
- 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.