TopolearnChoosing the key form changes storage, id reuse and performance in ways specific to SQLite.
Every ordinary SQLite table has a hidden 64-bit integer key, the rowid. The table is stored as a B-tree ordered by rowid, so looking up a row by rowid is the fastest access there is. You can select it as rowid.
If you declare a column exactly as INTEGER PRIMARY KEY, that column becomes an alias for the rowid rather than a separate column. Insert NULL (or omit it) and SQLite assigns a value: normally one more than the current largest rowid. Note the exact spelling matters: INT PRIMARY KEY or BIGINT PRIMARY KEY is not an alias; it creates an ordinary column with a separate unique index.
Plain INTEGER PRIMARY KEY can reuse ids: if you delete the row with the largest id, the next insert may get the same id again. Adding AUTOINCREMENT guarantees ids are never reused, using a bookkeeping table sqlite_sequence. It costs a little extra work per insert; SQLite's docs recommend it only when non-reuse genuinely matters.
WITHOUT ROWID tables store rows in a B-tree ordered by the declared primary key instead. They save space and a lookup for tables with non-integer or composite primary keys and small rows, such as junction tables. They require an explicit PRIMARY KEY.
From Python, cursor.lastrowid returns the new rowid; in SQL, last_insert_rowid() or a RETURNING id clause.
Example
import sqlite3
con = sqlite3.connect(":memory:")
con.executescript('''
CREATE TABLE a (id INTEGER PRIMARY KEY, v TEXT); -- alias for rowid
CREATE TABLE b (id INTEGER PRIMARY KEY AUTOINCREMENT, v TEXT); -- never reuses ids
CREATE TABLE tag_link (post_id INT, tag TEXT, PRIMARY KEY (post_id, tag)) WITHOUT ROWID;
''')
for t in ("a", "b"):
con.execute(f"INSERT INTO {t} (v) VALUES ('x'), ('y'), ('z')")
con.execute(f"DELETE FROM {t} WHERE id = 3")
new_id = con.execute(f"INSERT INTO {t} (v) VALUES ('w') RETURNING id").fetchone()[0]
print(t, new_id)
# a 3 <- reused
# b 4 <- AUTOINCREMENT never reuses
print(con.execute("SELECT rowid, id FROM a WHERE id = 1").fetchone()) # (1, 1)
print(con.execute("SELECT * FROM sqlite_sequence").fetchall()) # [('b', 4)]Remember
- INTEGER PRIMARY KEY (exact spelling) aliases the rowid
- Without AUTOINCREMENT, the largest deleted id can be reused
- WITHOUT ROWID suits composite or non-integer keys with small rows
Watch out for
- Declaring INT PRIMARY KEY and expecting automatic ids: only INTEGER PRIMARY KEY aliases the rowid.