SQLite AUTOINCREMENT vs ROWID: Why AUTOINCREMENT Is Usually Unnecessary
By Sharon Ben-Moshe · October 10, 2026 · 1 min read
Sharon Ben-Moshe is the founder of sqlfmt, a browser-based SQL formatter and validator with misspelled-keyword suggestions across PostgreSQL, MySQL, SQLite, SQL Server, and BigQuery.
Understand SQLite's 64-bit rowid vs the AUTOINCREMENT keyword. Discover why AUTOINCREMENT adds performance overhead and when to use it.
Quick answer
In SQLite, declaring a column as 'INTEGER PRIMARY KEY' automatically makes it an alias for SQLite's internal 64-bit signed integer rowid, auto-generating sequentially increasing IDs by default. The 'AUTOINCREMENT' keyword should only be added if you strictly need to prevent the reuse of previously deleted IDs, as it adds table overhead and CPU costs.
FAQs
Does INTEGER PRIMARY KEY auto-increment without the AUTOINCREMENT keyword?
Yes! When an insert omits the ID or provides NULL, SQLite automatically assigns an unused integer 1 greater than the largest integer rowid previously in the table.
What does the AUTOINCREMENT keyword actually do in SQLite?
AUTOINCREMENT prevents SQLite from recycling deleted IDs if the 64-bit integer limit is ever reached. It records the highest issued ID in the internal `sqlite_sequence` table and guarantees that every new ID is strictly greater than any ID ever used in that table.
Why does the official SQLite documentation advise against AUTOINCREMENT?
The SQLite documentation explicitly warns: 'The AUTOINCREMENT keyword imposes extra CPU, memory, disk space, and disk I/O overhead and should be avoided if not strictly needed.'
Does INT PRIMARY KEY alias the rowid?
No! In SQLite, only the exact spelling 'INTEGER PRIMARY KEY' (with INTEGER spelled out) creates a rowid alias. 'INT PRIMARY KEY' or 'BIGINT PRIMARY KEY' creates an ordinary indexed column with integer affinity that does NOT auto-increment.
Keep reading
The Comprehensive Guide to SQL Data Types: Storage, Precision, and Cross-Dialect Portability
By Sharon Ben-Moshe · Oct 13, 2026
The complete guide to SQL data types across PostgreSQL, MySQL, SQL Server, and SQLite. Numeric precision, character sets, timestamps, and JSON.
Read postThe Definitive Guide to SQL Window Functions: Syntax, Framing, and Real-World Recipes
By Sharon Ben-Moshe · Oct 13, 2026
The comprehensive guide to SQL window functions. Master OVER, PARTITION BY, ORDER BY, window frames (ROWS vs RANGE), and ranking recipes.
Read postTry it now
Paste your SQL into sqlfmt — format, validate, and catch typos free in your browser.