What does SQLite AUTOINCREMENT change about row IDs?

For an ordinary SQLite rowid table, INTEGER PRIMARY KEY aliases the rowid. If you leave the key out, or insert NULL for it, SQLite assigns an unused integer. Without AUTOINCREMENT, it usually chooses one greater than the largest row ID currently in the table. That means a deleted ID can sometimes be reused, including after deleting the highest-ID row. [S1]

With INTEGER PRIMARY KEY AUTOINCREMENT, SQLite changes automatic allocation: it chooses IDs greater than any row ID previously committed for that table, so it will not reuse a previously committed ID. [S1]

For example, assuming a disposable table named items, these two definitions show the choice:

CREATE TABLE items (id INTEGER PRIMARY KEY, label TEXT);

CREATE TABLE items (id INTEGER PRIMARY KEY AUTOINCREMENT, label TEXT);

Choose one definition, not both. When inserting a label, leave id out of the inserted columns to let SQLite assign it. This distinction concerns automatically selected IDs; it does not turn IDs into a business numbering system.

No. AUTOINCREMENT prevents automatic reuse of previously committed row IDs in the documented table and key conditions; it does not promise gapless numbering or a step of exactly one. Failed inserts can leave gaps. [S1]

For instance, if a disposable example produces IDs 1, 2, and 4, the missing 3 does not mean the key is broken. The important guarantee is about not automatically reusing a previously committed ID, not about every number appearing in order. [S1]

So decide what “only go up” needs to mean. If you mean “do not automatically bring back a committed ID after its row is deleted,” AUTOINCREMENT addresses that. If you mean “a human-facing sequence with no gaps,” this feature does not meet that requirement. The source describes row-ID allocation; it does not establish rules for invoices, tickets, or other business records.

There is a tradeoff: SQLite says AUTOINCREMENT adds CPU, memory, disk-space, and I/O overhead, and is usually unnecessary. [S1] That makes “add it just in case” a weak default when you do not need the no-reuse behavior.

A practical decision for a small app is:

1. Keep INTEGER PRIMARY KEY if you want SQLite to assign row IDs and do not need to prevent reuse of a previously committed ID.
2. Choose INTEGER PRIMARY KEY AUTOINCREMENT if preventing that automatic reuse is an actual requirement for this table.
3. In either case, do not depend on the IDs being gapless or consecutive. [S1]

For example, assuming a disposable notes table where an internal row key is only used to identify the current row, the ordinary key is a reasonable starting choice. That example is a design illustration, not a claim about what every application needs.

First, confirm the requirement in plain language: “We must not automatically reuse a row ID that was committed before and later deleted.” If nobody needs that behavior, the ordinary INTEGER PRIMARY KEY avoids the documented overhead of AUTOINCREMENT. If the requirement is instead “numbers must be gapless,” do not treat AUTOINCREMENT as the solution; SQLite explicitly does not make that guarantee. [S1]

Next, check that you are discussing the relevant SQLite case: a rowid table using INTEGER PRIMARY KEY. The cited allocation and reuse description is limited to SQLite’s documented table/key conditions; it does not establish behavior for every database, schema, or identifier design. [S1]

Copyable decision note, with placeholders to fill in:

“Table: [table name]. We use [INTEGER PRIMARY KEY / INTEGER PRIMARY KEY AUTOINCREMENT] because [specific row-ID reuse requirement or no such requirement]. IDs are [internal row identifiers / other clearly described purpose], not a promise of gapless numbering. We have considered the documented overhead of AUTOINCREMENT.”

Before applying a schema change, follow your project’s normal migration and review process. This guide explains the allocation choice; it does not prescribe a migration method.