Lesson 44 of 61

5 mins read

SQL AUTO INCREMENT & Sequences: Identity Generation

In Plain English

Surrogate primary keys identify records independently of business attributes. `AUTOINCREMENT` (SQLite), `AUTO_INCREMENT` (MySQL), `SERIAL` / `IDENTITY` (PostgreSQL / SQL Server) automatically compute the next integer ID when new rows are inserted. Understanding identity sequences prevents ID collisions and ensures gap-resistant identifier management.

The Mental Model

"`AUTO INCREMENT` is a deli ticket dispenser: every customer taking a ticket receives the next consecutive number without needing to know who came before."

Deep Dive: How It Works

Monotonic Growth: Guarantees every newly generated ID is greater than previous IDs.

Rollback Gaps: If an insert transaction rolls back, consumed sequence numbers are discarded, creating natural numbering gaps (which is normal and expected).

Engine Syntax Differences: SQLite uses `AUTOINCREMENT`, MySQL uses `AUTO_INCREMENT`, PostgreSQL uses `GENERATED ALWAYS AS IDENTITY`.

Syntax Blueprint

-- SQLite
id INTEGER PRIMARY KEY AUTOINCREMENT
-- MySQL
id INT AUTO_INCREMENT PRIMARY KEY
-- PostgreSQL
id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY

Defines self-incrementing primary key columns across major SQL dialects.

Core Rules to Remember

Zero Insert Overhead: Clients omit the ID column during insertion; the engine assigns it automatically.

Sequence Gaps are Normal: Failed transactions consume sequence numbers without rollback.

Surrogate Keys: Provides stable, immutable relational anchors for foreign keys.

Common Beginner Traps & How to Fix Them

Mistake:Assuming auto-increment numbers will always be 100% contiguous without any missing numbers.

Why it happens: Rollbacks, batch inserts, and deletions naturally produce gaps in the sequence.

How to fix: Never rely on ID continuity for business logic (use invoice sequences or separate counters if strict contiguity is legally required).

Pro Tip: In SQLite, a column declared `INTEGER PRIMARY KEY` auto-increments by default without needing the expensive `AUTOINCREMENT` keyword unless strict monotonic ID reuse prevention is required.

Live Interactive Example

Hit Run Code to see it live

Automatic Key Generation Across Inserts

Python 3.12

Output Console

Click "Run Code" to view the rendered output.

How it works: Each row received the next consecutive integer ID automatically.

Your Turn: Micro Challenge

No pressure! Edit the starter code below and test your solution with instant feedback.

Micro Exercise

Auto Increment Keys

Create a table `tickets (id INTEGER PRIMARY KEY, title TEXT NOT NULL)`.
Insert two tickets ("Bug #1" and "Feature #2"), then select all rows.

Sandbox Output

Click "Run & Check" to test your solution.

Finished reading and practicing?

Mark this lesson as completed to update your course progress.