If you have worked with other database systems before coming to SQLite, you have probably used something like AUTO_INCREMENT in MySQL or a SERIAL type in PostgreSQL to automatically generate unique numeric identifiers for new rows. SQLite has its own version of this idea, the AUTOINCREMENT keyword, but it behaves quite differently under the hood, and honestly, in most cases you do not even need it. In this article, I want to clear up exactly what AUTOINCREMENT does in SQLite, how it differs from SQLite’s default rowid behavior, and when you should actually reach for it.
SQLite’s Default Behavior: ROWID and INTEGER PRIMARY KEY
To understand AUTOINCREMENT, you first need to understand how SQLite handles primary keys by default. Every table in SQLite (unless it is declared WITHOUT ROWID) has a hidden column called rowid, which is a unique 64-bit signed integer that identifies each row. When you declare a column as INTEGER PRIMARY KEY, that column becomes an alias for the rowid column itself.
CREATE TABLE tasks (
id INTEGER PRIMARY KEY,
description TEXT NOT NULL
);
Here, id is literally the same thing as the internal rowid. When you insert a new row without specifying a value for id, SQLite automatically assigns it one, and by default, that value is simply one greater than the largest rowid currently in the table:
INSERT INTO tasks (description) VALUES ('Buy groceries');
INSERT INTO tasks (description) VALUES ('Walk the dog');
SELECT * FROM tasks;
-- 1 | Buy groceries
-- 2 | Walk the dog
This works well for the vast majority of use cases, and it is what most SQLite tutorials show you without ever mentioning AUTOINCREMENT at all. In fact, many experienced SQLite developers, myself included, use plain INTEGER PRIMARY KEY far more often than AUTOINCREMENT.
So What Does AUTOINCREMENT Actually Add?
The key difference is about what happens when rows get deleted, specifically the highest-numbered row.
With plain INTEGER PRIMARY KEY, if you delete the row with the highest ID and then insert a new row, SQLite may reuse that now-available ID. Here is an example:
CREATE TABLE tasks (
id INTEGER PRIMARY KEY,
description TEXT NOT NULL
);
INSERT INTO tasks (description) VALUES ('Task A'); -- id = 1
INSERT INTO tasks (description) VALUES ('Task B'); -- id = 2
INSERT INTO tasks (description) VALUES ('Task C'); -- id = 3
DELETE FROM tasks WHERE id = 3;
INSERT INTO tasks (description) VALUES ('Task D'); -- id = 3 again!
Notice that “Task D” got assigned id = 3, the same ID that previously belonged to the now-deleted “Task C.” In most applications, this is completely harmless. But in some specific situations, reusing a previously deleted ID can actually cause problems, particularly if your application caches references to specific IDs elsewhere, such as in logs, external systems, exported files, or URLs that were shared before the row was deleted. If something else in your system still refers to the old “Task C” by ID 3, and a brand new, completely unrelated row now also has ID 3, you can end up with confusing or even incorrect behavior.
This is exactly the problem AUTOINCREMENT solves. When you add the AUTOINCREMENT keyword, SQLite guarantees that a new row will never reuse an ID that has been used before in that table, even if the row with that ID was later deleted.
CREATE TABLE tasks (
id INTEGER PRIMARY KEY AUTOINCREMENT,
description TEXT NOT NULL
);
INSERT INTO tasks (description) VALUES ('Task A'); -- id = 1
INSERT INTO tasks (description) VALUES ('Task B'); -- id = 2
INSERT INTO tasks (description) VALUES ('Task C'); -- id = 3
DELETE FROM tasks WHERE id = 3;
INSERT INTO tasks (description) VALUES ('Task D'); -- id = 4, not 3
With AUTOINCREMENT in place, “Task D” gets id = 4, guaranteeing that ID 3 is never reused, even though it is now available.
How AUTOINCREMENT Works Internally
SQLite implements this guarantee using a special internal table called sqlite_sequence. Whenever you create a table with an AUTOINCREMENT column, SQLite automatically creates (or updates) this hidden bookkeeping table, which tracks the highest ID value ever used for each AUTOINCREMENT table in your database.
SELECT * FROM sqlite_sequence;
This will show you something like:
name seq
tasks 4
Every time you insert a new row, SQLite checks this table, takes the recorded value, adds one, uses that as the new row’s ID, and updates the record accordingly. This is what guarantees monotonically increasing values that never repeat, even across deletions.
The Trade-Off: Performance
This extra bookkeeping does not come for free. Using AUTOINCREMENT requires SQLite to maintain and check the sqlite_sequence table on every insert, which introduces a small amount of additional overhead compared to the default rowid behavior. For most applications, this overhead is genuinely negligible and not something you would ever notice. But it is worth knowing about, especially if you are working on a performance-critical system doing extremely high volumes of inserts.
The official SQLite documentation itself recommends against using AUTOINCREMENT unless you specifically need the guarantee it provides, precisely because of this performance cost and because the default behavior is sufficient for the overwhelming majority of use cases.
When You Should Use AUTOINCREMENT
Given everything above, here is my honest guidance on when reaching for AUTOINCREMENT actually makes sense.
Use it when IDs are exposed externally and reuse would cause real problems. For example, if your application generates public URLs or API references based on row IDs, and old links or cached references to a deleted ID might still be floating around, you do not want a brand new, unrelated record accidentally taking over that same ID.
Use it when you are logging or auditing based on IDs, and you need a strict guarantee that an ID always refers to exactly one specific record for the lifetime of your system, never getting recycled and potentially causing confusion in historical records.
Use it when integrating with external systems that assume ID uniqueness is permanent, such as syncing with another database or a third-party service that stores your row IDs as foreign references and expects them to remain permanently unique.
When You Should Skip AUTOINCREMENT
In the vast majority of everyday applications. If you are building a typical CRUD application, a prototype, an internal tool, or something where ID reuse after deletion genuinely does not matter, plain INTEGER PRIMARY KEY is simpler, slightly faster, and perfectly adequate.
When you care about maximizing insert performance, particularly in write-heavy applications performing large batch inserts, since avoiding the sqlite_sequence bookkeeping overhead adds up at scale.
When you plan to use UUIDs or another externally generated unique identifier anyway. Many modern applications, especially those that need to generate IDs client-side before syncing with a server, use UUIDs (stored as TEXT) instead of relying on SQLite’s automatic numbering at all. In that case, AUTOINCREMENT is irrelevant regardless.
A Common Misconception
A mistake I see fairly often is developers assuming AUTOINCREMENT is required to make a primary key auto-generate values at all. That is not true. Plain INTEGER PRIMARY KEY already auto-generates sequential values without you needing to specify AUTOINCREMENT. The keyword does not enable auto-numbering; auto-numbering already happens by default. What AUTOINCREMENT adds is specifically the guarantee against ID reuse, nothing more.
Practical Example: Comparing Both Approaches
Let’s look at a side-by-side comparison to make the distinction completely clear.
-- Without AUTOINCREMENT
CREATE TABLE comments_v1 (
id INTEGER PRIMARY KEY,
body TEXT NOT NULL
);
-- With AUTOINCREMENT
CREATE TABLE comments_v2 (
id INTEGER PRIMARY KEY AUTOINCREMENT,
body TEXT NOT NULL
);
If you insert three rows into each table, delete the last one, and insert a new row, comments_v1 will reuse the deleted row’s ID, while comments_v2 will not. Functionally, both approaches give you a unique, auto-generated integer identifier for every row that currently exists in the table at any given moment. The difference only becomes visible after a delete-then-insert sequence, which is exactly why so many developers go a long time without ever needing to think about this distinction at all.
Important Restrictions on AUTOINCREMENT
There are a couple of rules worth knowing. AUTOINCREMENT can only be used together with INTEGER PRIMARY KEY, on a column that holds exactly this declared type. You cannot use it on a TEXT primary key, a composite primary key, or a WITHOUT ROWID table, since the entire mechanism is built on top of SQLite’s rowid system.
Also, because the maximum value ever used is tracked and never reused, an AUTOINCREMENT table will eventually raise an SQLITE_FULL error if it somehow reaches the maximum possible value for a 64-bit signed integer. This is an astronomically large number in practice, and essentially no real-world application will ever hit this limit, but it is a theoretical edge case worth being aware of if you are designing a system meant to run indefinitely at extremely high insert volumes.
Best Practices
Default to plain INTEGER PRIMARY KEY unless you have a specific, articulable reason to need AUTOINCREMENT‘s no-reuse guarantee. Document why you are using AUTOINCREMENT when you do use it, so future maintainers of your codebase understand the intentional trade-off rather than assuming it was added out of habit or misunderstanding. Consider UUIDs instead of relying on either approach if you need globally unique identifiers that work across multiple distributed databases or need to be generated before a row is ever inserted into the database. Avoid designing your application logic to depend on specific numeric ID values having any meaning beyond uniqueness, since doing so tends to create fragile systems regardless of which numbering approach you choose.
Wrapping Up
The AUTOINCREMENT keyword in SQLite is a narrowly scoped feature that solves one specific problem: guaranteeing that primary key values are never reused, even after the row that held them has been deleted. It is not required for basic auto-numbering, which SQLite already provides by default through its rowid mechanism whenever you declare an INTEGER PRIMARY KEY column. Understanding this distinction means you can make an informed choice for each table in your schema, rather than blindly adding AUTOINCREMENT everywhere out of habit carried over from other database systems, or skipping it in situations where its guarantee genuinely matters. For most everyday tables, the plain INTEGER PRIMARY KEY approach will serve you perfectly well, and reserving AUTOINCREMENT for the specific cases where ID stability truly matters is the more thoughtful, informed way to design your schema.
