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

Ninad Pathak

October 7, 2026

Every ordinary SQLite table already numbers its rows, so a new row gets an ID without AUTOINCREMENT. The keyword adds one guarantee, that IDs from deleted rows never come back, and SQLite’s docs say to skip it unless you need that. In this article I’ll walk you through how SQLite assigns IDs and when AUTOINCREMENT is worth adding.

TLDR

Declare id INTEGER PRIMARY KEY, and SQLite gives each new row the next number automatically.
AUTOINCREMENT adds one guarantee: an id from a deleted row is never handed out again.

SQLite’s docs say AUTOINCREMENT “should be avoided if not strictly needed”.
Get the new id with last_insert_rowid() or INSERT … RETURNING id.

What is AUTOINCREMENT in SQLite?

AUTOINCREMENT is a keyword you add after INTEGER PRIMARY KEY to stop SQLite from reusing ids. Without it, SQLite already numbers rows: the new id is normally “one larger than the largest ROWID in the table prior to the insert” (SQLite Autoincrement).

The docs are blunt about the cost: “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 syntax

The two forms differ by one keyword:

CREATE TABLE table_name (id INTEGER PRIMARY KEY, ...);                -- auto-numbered, ids can be reused
CREATE TABLE table_name (id INTEGER PRIMARY KEY AUTOINCREMENT, ...);  -- auto-numbered, ids never reused

	`INTEGER PRIMARY KEY`	`INTEGER PRIMARY KEY AUTOINCREMENT`
New id	One more than the current largest	One more than the largest used before
Id of a deleted last row	Can be used again	Never used again
Extra bookkeeping	None	A row in `sqlite_sequence`

The type must be exactly INTEGER.

How to auto-increment ids in SQLite

To show you the difference, I will create a notes table without AUTOINCREMENT, then the same table with it. Each step runs on the same database, one statement after another.

Step 1: Let INTEGER PRIMARY KEY number the rows

Leave the id out of the insert, and SQLite fills it in:

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

Step 2: See an id reused after a delete

Delete the row with the largest id, and that id is used again for the next insert:

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

Step 3: Add AUTOINCREMENT to stop reuse

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”. 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

Examples

These examples continue on the same tables.

Insert 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 a new row

last_insert_rowid() returns it for the current connection, so both statements here run in one session. 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

Edge cases

AUTOINCREMENT has two rules that only show up when something goes wrong.

AUTOINCREMENT needs INTEGER, not INT

INT PRIMARY KEY AUTOINCREMENT is rejected:

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.

When ids run out

The largest possible id is 9223372036854775807. Without AUTOINCREMENT, once that id exists, SQLite “starts picking positive candidate ROWIDs at random until it finds one that is not previously used”. The next insert still succeeds:

sqlite> CREATE TABLE p (id INTEGER PRIMARY KEY, v TEXT);
sqlite> INSERT INTO p VALUES (9223372036854775807, 'max'); INSERT INTO p (v) VALUES ('next');
sqlite> SELECT count(*) AS rows, sum(id > 0) AS positive_ids FROM p;
rows  positive_ids
----  ------------
2     2

With AUTOINCREMENT, “new INSERTs are not allowed and any attempt to insert a new row will fail with an SQLITE_FULL error”. SQLite reports that error as “database or disk is full”:

sqlite> CREATE TABLE a (id INTEGER PRIMARY KEY AUTOINCREMENT, v TEXT);
sqlite> INSERT INTO a VALUES (9223372036854775807, 'max');
sqlite> INSERT INTO a (v) VALUES ('next');
Error: stepping, database or disk is full (13)

Conclusion

Use id INTEGER PRIMARY KEY for auto-numbered rows. Add AUTOINCREMENT only when an id must never come back after its row is deleted, for example when old ids appear in logs or URLs. Read the new id with last_insert_rowid() or RETURNING.