Linking Tables with Foreign Keys

When a row is inserted into a table with an auto-incrementing primary key, the database assigns a unique ID automatically. The INSERT statement performs the insertion, but it does not by itself tell your application which ID was assigned. That missing value matters whenever a later operation must refer to the new row, such as linking a related record, displaying the row's ID, constructing a URL, or storing the ID in application state.

The reliable sequence has two immediate steps. First, execute the INSERT statement. The database validates the data, finds the next available ID, writes the row, and updates its tracking of the most recent inserted ID for the current connection. Next, execute SELECT last_insert_rowid();. The function reads that connection's tracked value and returns the ID assigned by the preceding insert.

The important boundary is between the database and the application. The database assigns and tracks the ID. The application asks for that ID, stores it, and decides where to use it next. Retrieval is therefore not the end of the workflow; it is the handoff that allows later operations to refer to the new row.

last_insert_rowid() is connection-local. Each database connection maintains its own record of the most recently inserted row ID. If one user inserts a row and receives ID 100 while another user inserts a row at the same time and receives ID 101, the first user's connection still returns 100 and the second user's connection returns 101. The inserts do not overwrite one another's retrieval results.

In an application, do not treat the returned ID as disposable output. Immediately capture it in application code after SELECT last_insert_rowid(). Keeping the value available lets the application use it for related records, construct URLs, display it to a user, or perform another operation that needs to identify the newly inserted row.