Autoincrement

In SQLite, most tables do not need AUTOINCREMENT.

INTEGER PRIMARY KEY already auto-generates row ids in a practical way for typical applications.

Core Concepts

flowchart LR
  A[Insert row without explicit id] --> B{Key definition}
  B -->|INTEGER PRIMARY KEY| C[SQLite picks rowid]
  B -->|INTEGER PRIMARY KEY AUTOINCREMENT| D[SQLite enforces never-reuse policy]

Definition	Behavior	Typical recommendation
INTEGER PRIMARY KEY	Auto id generation with low overhead	Preferred default
INTEGER PRIMARY KEY AUTOINCREMENT	Prevents reuse of previously used ids	Use only when required

Recommended Default

CREATE TABLE events (
  event_id INTEGER PRIMARY KEY,
  title    TEXT NOT NULL
);

Expected output
Table created; ids auto-generate on future inserts.

Never Reuse IDs

CREATE TABLE audit_log (
  log_id   INTEGER PRIMARY KEY AUTOINCREMENT,
  message  TEXT NOT NULL
);

Expected output
Table created with AUTOINCREMENT behavior enabled.

Code Examples

-- Compare schemas side-by-side.
.schema events
.schema audit_log

Expected output
events shows INTEGER PRIMARY KEY; audit_log shows AUTOINCREMENT.

SQLite-Specific Nuances

SQLite Nuance

AUTOINCREMENT in SQLite adds guarantees but also extra overhead.

Use it only if your business rules require that deleted ids are never reused.

Common Pitfalls / Best Practices

Pitfall

Adding AUTOINCREMENT everywhere "just in case".

This often adds complexity without real benefit.

Best Practice

Start with INTEGER PRIMARY KEY; adopt AUTOINCREMENT only when id non-reuse is a strict requirement.

Quick Challenge

You are designing a log table where external auditors require strictly non-reused ids. Which key style should you choose?

View Solution

Use:

log_id INTEGER PRIMARY KEY AUTOINCREMENT

Why
It enforces SQLite's non-reuse behavior for generated ids.