SQLite AUTOINCREMENT: When You Need It (and Why You Usually Don’t)

Ray Ferrell

October 6, 2026

In SQLite you usually don’t need AUTOINCREMENT. A column declared INTEGER PRIMARY KEY already gets the next number automatically. AUTOINCREMENT only adds one guarantee: numbers from deleted rows are never handed out again. SQLite’s own documentation is blunt: “The AUTOINCREMENT keyword imposes extra CPU, memory, disk space, and disk I/O overhead and should be avoided if not strictly needed. It is usually not needed.” (SQLite Autoincrement)

Use id INTEGER PRIMARY KEY for auto-numbered rows. Add AUTOINCREMENT (id INTEGER PRIMARY KEY AUTOINCREMENT) only if an id must never be reused after its row is deleted. The type must be exactly INTEGER.

Every example on this page was run on 6 October 2026 with SQLite 3.51.0 in the sqlite3 shell, one statement after another on the same database. Lines starting with sqlite> show the statement; the lines after it are its output.

INTEGER PRIMARY KEY numbers rows for you

sqlite> CREATE TABLE notes (id INTEGER PRIMARY KEY, body TEXT);
sqlite> INSERT INTO notes (body) VALUES ('a'), ('b'), ('c'); SELECT * FROM notes;
id  body
--  ----
1   a
2   b
3   c

Without AUTOINCREMENT, the new id is normally “one larger than the largest ROWID in the table prior to the insert” (SQLite Autoincrement). So if you delete the row with the largest id, that id is used again:

sqlite> DELETE FROM notes WHERE id = 3;
sqlite> INSERT INTO notes (body) VALUES ('d'); SELECT * FROM notes;
id  body
--  ----
1   a
2   b
3   d

AUTOINCREMENT never reuses ids

With AUTOINCREMENT, “The ROWID chosen for the new row is at least one larger than the largest ROWID that has ever before existed in that same table” (SQLite Autoincrement). The deleted id 3 is skipped:

sqlite> CREATE TABLE orders (id INTEGER PRIMARY KEY AUTOINCREMENT, item TEXT);
sqlite> INSERT INTO orders (item) VALUES ('a'), ('b'), ('c');
sqlite> DELETE FROM orders WHERE id = 3;
sqlite> INSERT INTO orders (item) VALUES ('d'); SELECT * FROM orders;
id  item
--  ----
1   a
2   b
4   d

SQLite remembers the highest id in an internal table, sqlite_sequence, created automatically for tables that use AUTOINCREMENT:

sqlite> SELECT * FROM sqlite_sequence;
name    seq
------  ---
orders  4

Inserting your own id

You can still insert an explicit id. Automatic numbering then continues from the highest value:

sqlite> INSERT INTO orders (id, item) VALUES (100, 'e');
sqlite> INSERT INTO orders (item) VALUES ('f'); SELECT * FROM orders WHERE id >= 100;
id   item
---  ----
100  e
101  f

Get the id of the row you just inserted

last_insert_rowid() returns it for the current connection. Here both statements ran in one session, because the value belongs to the connection. You can also add RETURNING id to the INSERT:

sqlite> INSERT INTO orders (item) VALUES ('g');
sqlite> SELECT last_insert_rowid();
last_insert_rowid()
-------------------
102

AUTOINCREMENT needs INTEGER, not INT

sqlite> CREATE TABLE bad (id INT PRIMARY KEY AUTOINCREMENT);
Error: in prepare, AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY

The same rule decides whether a primary key is the rowid at all; see INTEGER PRIMARY KEY in CREATE TABLE.

SQLite cheat sheet, CREATE TABLE, which internal tables .tables hides (including sqlite_sequence), and the official reference: SQLite Autoincrement.