DEV Community

Cover image for AUTOINCREMENT in SQLite
Athreya aka Maneshwar
Athreya aka Maneshwar

Posted on

AUTOINCREMENT in SQLite

#webdev #programming #database #architecture

Hello, I'm Maneshwar. I'm building git-lrc, an AI code reviewer that runs on every commit. It is free, unlimited, and source-available on Github. Star Us to help devs discover the project. Do give it a try and share your feedback for improving the product.

When working with SQLite, generating unique IDs is something you deal with almost immediately.

Most developers assume that AUTOINCREMENT is required for this, but SQLite already handles auto-incrementing behavior by default in a slightly different way.

Default Auto-Increment Behavior

If you define a column like this:

CREATE TABLE t1 (
    a INTEGER PRIMARY KEY
);
Enter fullscreen mode Exit fullscreen mode

SQLite will automatically assign values to a whenever you insert NULL or don’t provide a value at all.

For example:

INSERT INTO t1 VALUES (NULL);
Enter fullscreen mode Exit fullscreen mode

In most cases, SQLite assigns a value that is one greater than the current maximum in that column. If the table is empty, it starts from 1.

This makes it feel like standard auto-increment behavior, and for many applications, this is more than enough.

The Subtle Catch

The important detail is that SQLite only guarantees current uniqueness, not historical uniqueness.

If you:

  • Insert rows → 1, 2, 3
  • Delete row with ID 3
  • Insert again

SQLite may reuse 3.

This is not a bug. It is simply how SQLite optimizes ID generation.

It avoids keeping extra state and just looks at existing values.

For internal systems, this usually doesn’t matter. But if IDs are exposed outside (like APIs or logs), reuse can create confusion.

What AUTOINCREMENT Actually Does

When you explicitly use:

CREATE TABLE t1 (
    a INTEGER PRIMARY KEY AUTOINCREMENT
);
Enter fullscreen mode Exit fullscreen mode

SQLite switches to a stricter strategy.

Now, instead of checking the current max value, it remembers the largest value ever used and always generates a new value greater than that.

So even if you delete rows, old IDs are never reused.

This gives you a stronger guarantee:

  • IDs are unique across the entire lifetime of the table
  • No accidental reuse

The Role of sqlite_sequence

To make this work, SQLite maintains a special internal table called sqlite_sequence.

It stores:

  • Table name
  • Highest ID ever used

This table is created automatically when you first insert into a table that uses AUTOINCREMENT.

Each new insert updates this value so SQLite always knows what comes next.

Why Not Always Use AUTOINCREMENT?

At first glance, AUTOINCREMENT seems like the safer option. But it comes with tradeoffs.

  • It adds a small performance overhead
  • It requires maintaining extra state (sqlite_sequence)
  • It prevents reuse, which may not always be necessary

Also, SQLite has a limit on integer values. If the maximum value is ever reached, further inserts will fail.

SQLite already gives you auto-incrementing IDs without needing AUTOINCREMENT.

The keyword is only for stricter guarantees, not basic functionality.

Instead of blindly adding AUTOINCREMENT, you choose it only when your system actually needs that level of consistency.

git-lrc
*AI agents write code fast. They also silently remove logic, change behavior, and introduce bugs -- without telling you. You often find out in production.

git-lrc fixes this. It hooks into git commit and reviews every diff before it lands. 60-second setup. Completely free.*

Any feedback or contributors are welcome! It's online, source-available, and ready for anyone to use.

⭐ Star it on GitHub:

GitHub logo HexmosTech / git-lrc

Free, Unlimited AI Code Reviews That Run on Commit



AI agents write code fast. They also silently remove logic, change behavior, and introduce bugs -- without telling you. You often find out in production.

git-lrc fixes this. It hooks into git commit and reviews every diff before it lands. 60-second setup. Completely free.

See It In Action

See git-lrc catch serious security issues such as leaked credentials, expensive cloud operations, and sensitive material in log statements

git-lrc-intro-60s.mp4

Why

  • 🤖 AI agents silently break things. Code removed. Logic changed. Edge cases gone. You won't notice until production.
  • 🔍 Catch it before it ships. AI-powered inline comments show you exactly what changed and what looks wrong.
  • 🔁 Build a…
View on GitHub

Top comments (0)