How to get the ID of an inserted row in SQL

1INSERT … RETURNING idRecommended

A RETURNING clause at the end of an INSERT makes the statement return the rows it just wrote, like a SELECT. List the columns you want: the generated id, and anything else the database filled in, such as a default value. With a multi-row insert (see insert multiple rows) you get one result row per inserted row, so all the new ids come back from one statement.

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    plan TEXT NOT NULL DEFAULT 'free'
);

-- The new id comes back as a result row
INSERT INTO users (email)
VALUES ('[email protected]')
RETURNING id;

-- Ask for several columns, including ones the database filled in
INSERT INTO users (email)
VALUES ('[email protected]')
RETURNING id, email, plan;

-- A multi-row insert returns one row per inserted row
INSERT INTO users (email, plan)
VALUES ('[email protected]', 'pro'),
       ('[email protected]', 'team')
RETURNING id, email, plan;

The first insert returns id 1. The second returns 2 together with plan = free, the default the table supplied, so you don't need a second query to see it. The multi-row insert returns ids 3 and 4. When you match returned rows back to the data you sent, match on a unique column such as email rather than on position. RETURNING needs SQLite 3.35 or later (the examples run on 3.53); it also works on UPDATE and DELETE. PostgreSQL has the same clause, MySQL doesn't, and SQL Server's version is OUTPUT INSERTED.id.

2last_insert_rowid(): the last id on this connection

Before RETURNING, SQLite code read the id afterwards with last_insert_rowid(). It reports the rowid of the most recent successful insert on the current connection. That makes it safe when other connections insert at the same time, but it is one value per connection, not per table, and it only remembers the last row.

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL UNIQUE
);
CREATE TABLE logins (
    id INTEGER PRIMARY KEY,
    user_id INTEGER NOT NULL
);

INSERT INTO users (name) VALUES ('Ada');
SELECT last_insert_rowid() AS new_id;

-- A multi-row insert: you get the id of the LAST row
INSERT INTO users (name) VALUES ('Grace'), ('Linus'), ('Margaret');
SELECT last_insert_rowid() AS new_id;

-- It is per connection, not per table: any insert replaces it
INSERT INTO logins (id, user_id) VALUES (100, 1);
SELECT last_insert_rowid() AS new_id;

-- A row that was not inserted leaves it unchanged
INSERT OR IGNORE INTO users (name) VALUES ('Ada');
SELECT last_insert_rowid() AS new_id, changes() AS inserted;

Ada gets 1. After the three-row insert it reports 4, the id of Margaret, the last row; the ids of Grace and Linus (2 and 3) aren't available from it, which is why RETURNING is the better tool for multi-row inserts. The insert into logins replaces the value with 100, even though it is a different table. The ignored duplicate inserts nothing (changes() is 0), so the value stays 100: always check that a row was actually inserted before trusting it. Your driver usually exposes the same number, for example cursor.lastrowid in Python or lastInsertRowid in Node.

3INTEGER PRIMARY KEY, the rowid and AUTOINCREMENT

Every ordinary SQLite table has a hidden integer key called rowid. A column declared INTEGER PRIMARY KEY becomes another name for it, which is why leaving the id out of an insert fills it in. The new value is normally one more than the largest id in the table, so if you delete the newest row, its id can be handed out again. Adding AUTOINCREMENT stops that: SQLite remembers the highest id it ever used and never goes back.

CREATE TABLE plain (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE auto (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT);

INSERT INTO plain (name) VALUES ('a'), ('b'), ('c');
INSERT INTO auto (name) VALUES ('a'), ('b'), ('c');

-- Delete the newest row in each table, then add one more
DELETE FROM plain WHERE id = 3;
DELETE FROM auto WHERE id = 3;
INSERT INTO plain (name) VALUES ('d');
INSERT INTO auto (name) VALUES ('d');

-- id and rowid are the same column
SELECT 'plain' AS tbl, rowid AS row_id, id, name FROM plain
UNION ALL
SELECT 'auto', rowid, id, name FROM auto;

-- Only the type name INTEGER does this: INT PRIMARY KEY is a
-- separate column, and nothing fills it in
CREATE TABLE wrong (id INT PRIMARY KEY, name TEXT);
INSERT INTO wrong (name) VALUES ('a')
RETURNING id, rowid AS row_id;

In plain, the new row d gets id 3 again, the id of the row that was just deleted. In auto it gets 4, because AUTOINCREMENT keeps the high-water mark in the internal sqlite_sequence table. In both, rowid and id match. Use AUTOINCREMENT only when a reused id would cause trouble (an old link or log entry pointing at a new row); without it SQLite does a little less work per insert. The last query shows a common mistake: INT PRIMARY KEY is not an alias for the rowid, so id comes back NULL while the hidden rowid is 1. Spell it INTEGER.

4Use the new id for child rows

The usual reason to want the id is to insert rows that point at it: the items of a new order, the addresses of a new customer. In application code, run the parent insert with RETURNING id, read the value, and pass it as a parameter to the child inserts. In a plain SQL script, be careful with last_insert_rowid(): every child row you insert changes it.

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    ref TEXT NOT NULL UNIQUE
);
CREATE TABLE items (
    id INTEGER PRIMARY KEY,
    order_id INTEGER NOT NULL REFERENCES orders (id),
    sku TEXT NOT NULL
);

-- Wrong: last_insert_rowid() moves on after every item row
INSERT INTO orders (id, ref) VALUES (100, 'A-100');
INSERT INTO items (order_id, sku)
VALUES (last_insert_rowid(), 'pen'),
       (last_insert_rowid(), 'ink'),
       (last_insert_rowid(), 'pad');

-- Right: save the new id once, then read the saved copy
INSERT INTO orders (ref) VALUES ('A-101');
CREATE TEMP TABLE new_order AS SELECT last_insert_rowid() AS id;
INSERT INTO items (order_id, sku)
SELECT new_order.id, v.column1
FROM new_order, (VALUES ('pen'), ('ink')) AS v;

-- Also right: find the parent again by its unique key
INSERT INTO orders (ref) VALUES ('A-102');
INSERT INTO items (order_id, sku)
SELECT id, 'cap' FROM orders WHERE ref = 'A-102';

SELECT i.id, i.order_id, o.ref, i.sku
FROM items AS i
LEFT JOIN orders AS o ON o.id = i.order_id;

The first order is created with id 100 so the bug is easy to see. Only pen gets 100: once it is inserted, last_insert_rowid() is the item's own id, so ink points at order 1 and pad at order 2, neither of which exists (the NULL refs). SQLite accepted them because foreign keys are off by default; after PRAGMA foreign_keys = ON the same insert fails with FOREIGN KEY constraint failed. Copying the id into a temp table first fixes it (both items of A-101 get 101), and so does looking the parent up by a unique column (A-102). SQLite doesn't allow an INSERT … RETURNING inside WITH; PostgreSQL does, which lets it insert the parent and its children in one statement (see the next table).

5Other databases

Each database has its own function for "the id I just generated", and they disagree about multi-row inserts: MySQL reports the first new id, SQLite and SQL Server the last. Every form below was run on PostgreSQL 18, MySQL 8.4 and SQL Server's T-SQL (through Babelfish). Oracle uses RETURNING id INTO a variable in PL/SQL.