SQLite Basics: Choose Between INTEGER PRIMARY KEY and AUTOINCREMENT

An ordinary SQLite table can assign integer IDs without the AUTOINCREMENT keyword. That keyword changes reuse rules, not whether automatic IDs exist. Understanding the difference helps avoid unnecessary overhead and false assumptions about gaps or deleted IDs.

Tested on Linux with Python 3.12.14 and SQLite 3.53.1. All three SQL files below were executed through the Python runner, and the output shown is observed output. The standalone SQLite CLI is not used.

Prepare a scratch directory

You need Bash, Python 3 with SQLite support, a text editor, and mktemp, rm, and rmdir. No additional Python packages or SQLite CLI are needed. Keep the same terminal open and save files as UTF-8. Stop if setup fails before saving any example files.

start_dir="$PWD"
work_dir="$(mktemp -d)" || exit 1
cd -- "$work_dir" || exit 1

Save as run-sql.py. Each invocation uses a fresh in-memory database. The runner prints pipe-separated rows, showing missing values as NULL, but does not escape embedded pipes or newlines. Put each statement’s ending semicolon at the end of a line, and do not put two statements on one line.

from pathlib import Path
import sqlite3
import sys

connection = sqlite3.connect(":memory:", isolation_level=None)
try:
    statement = ""
    for line in Path(sys.argv[1]).read_text(encoding="utf-8").splitlines(True):
        statement += line
        if sqlite3.complete_statement(statement):
            cursor = connection.execute(statement)
            for row in cursor:
                print("|".join("NULL" if value is None else str(value) for value in row))
            statement = ""
    if statement.strip():
        raise ValueError("Unfinished SQL statement")
finally:
    connection.close()

The examples are independent and modify only disposable toy tables. They use ordinary rowid tables, not STRICT or WITHOUT ROWID tables.

1. Give the automatic rowid a declared name

Save as compare-integer-key-spelling.sql:

CREATE TABLE notes (
    id INTEGER PRIMARY KEY,
    label TEXT NOT NULL
);
INSERT INTO notes(label) VALUES ('First'), ('Second');
SELECT id, rowid, label FROM notes ORDER BY id;
CREATE TABLE different_key (
    id INT PRIMARY KEY,
    label TEXT NOT NULL
);
INSERT INTO different_key(label) VALUES ('No id supplied');
SELECT rowid, id, label FROM different_key ORDER BY rowid;

Run:

python3 run-sql.py compare-integer-key-spelling.sql

Observed output:

1|1|First
2|2|Second
1|NULL|No id supplied

The notes table exposes the same key as both id and rowid. Omitting id generates values without AUTOINCREMENT. In the second declaration, INT is not the exact type name INTEGER, so id is a separate column rather than a rowid alias. Its missing value remains NULL in this ordinary table. INTEGER PRIMARY KEY rules.

The second table is a diagnostic example, not a recommended nullable key. Do not shorten INTEGER to INT when you intend this automatic-key behavior. STRICT and WITHOUT ROWID tables have additional primary-key rules, so this NULL result is not a rule for every SQLite table.

2. Compare deletion and subsequent automatic allocation

Save as compare-deleted-id-reuse.sql:

CREATE TABLE ordinary_ids (
    id INTEGER PRIMARY KEY,
    label TEXT NOT NULL
);
CREATE TABLE retained_ids (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    label TEXT NOT NULL
);
INSERT INTO ordinary_ids(label) VALUES ('First'), ('Second');
INSERT INTO retained_ids(label) VALUES ('First'), ('Second');
DELETE FROM ordinary_ids WHERE id = 2;
DELETE FROM retained_ids WHERE id = 2;
INSERT INTO ordinary_ids(label) VALUES ('Replacement');
INSERT INTO retained_ids(label) VALUES ('Replacement');
SELECT 'ordinary', id, label FROM ordinary_ids ORDER BY id;
SELECT 'autoincrement', id, label FROM retained_ids ORDER BY id;
SELECT name, seq FROM sqlite_sequence WHERE name = 'retained_ids';

Run:

python3 run-sql.py compare-deleted-id-reuse.sql

Observed output:

ordinary|1|First
ordinary|2|Replacement
autoincrement|1|First
autoincrement|3|Replacement
retained_ids|3

Deleting the highest ordinary ID lets the next automatic insert reuse 2 here. The AUTOINCREMENT table advances to 3 instead. Its sqlite_sequence row records the high-water mark; inspect that internal state rather than editing it to reset counters. AUTOINCREMENT behavior.

This is about automatic allocation. A caller can still explicitly supply an unused older ID. AUTOINCREMENT also does not turn an integer key into an authorization secret or a globally unique identifier.

Use the keyword when its non-reuse guarantee for committed IDs is actually part of your design. It adds bookkeeping overhead. For many ordinary local tables, INTEGER PRIMARY KEY is sufficient. AUTOINCREMENT tradeoffs.

3. Observe rollback reuse and a skipped value

Save as inspect-rollback-and-id-gaps.sql:

CREATE TABLE events (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    label TEXT NOT NULL UNIQUE
);
INSERT INTO events(label) VALUES ('Kept');
BEGIN;
INSERT INTO events(label) VALUES ('Trial');
SELECT 'uncommitted', id, label FROM events WHERE label = 'Trial';
ROLLBACK;
INSERT INTO events(label) VALUES ('After rollback');
INSERT OR IGNORE INTO events(label) VALUES ('Kept');
SELECT 'duplicate inserted', changes();
INSERT INTO events(label) VALUES ('After duplicate');
SELECT id, label FROM events ORDER BY id;
SELECT 'sequence', seq FROM sqlite_sequence WHERE name = 'events';

Run:

python3 run-sql.py inspect-rollback-and-id-gaps.sql

Observed output:

uncommitted|2|Trial
duplicate inserted|0
1|Kept
2|After rollback
4|After duplicate
sequence|4

The rolled-back trial used 2 temporarily, and the later accepted row receives 2 again. The ignored duplicate then leaves a gap before 4 in this run. Treat an ID observed inside an unfinished transaction as provisional, and never calculate row counts from the largest ID.

AUTOINCREMENT considers committed history, so rolled-back allocations can be reused. It promises increasing automatic IDs under its documented rules, not consecutive numbering. A rejected or ignored insertion can leave a gap. Rollback and sequence gaps.

OR IGNORE is included only to expose this edge case without stopping the runner. In an application, inspect and report skipped input rather than quietly assuming every attempted event was stored. A separate business sequence needs its own explicit rules; a surrogate row ID is not a gap-free counter.

Clean up

In the same terminal, remove only the named example files and the empty scratch directory:

rm -- run-sql.py compare-integer-key-spelling.sql compare-deleted-id-reuse.sql inspect-rollback-and-id-gaps.sql
cd -- "$start_dir" || exit 1
rmdir -- "$work_dir"
unset start_dir work_dir

Acknowledgements

This post was created with assistance from AI.