SQL AUTO INCREMENT

When you insert a new row into a table, you usually don’t want to think up a unique ID for it yourself — you want the database to generate one automatically. That’s what AUTO INCREMENT does: it tells the database engine to assign each new row a unique, ever-increasing numeric value for a column, almost always the primary key. This lesson covers how auto-incrementing columns work under the hood, the syntax across the major SQL dialects, worked examples, and the mistakes people make most often.

Overview: How AUTO INCREMENT Works

Every relational database needs a fast, reliable way to give each row an identity that never collides with another row’s identity, even as rows are inserted and deleted over time. Auto-increment columns solve this by having the engine track “the next number to hand out” internally and increment it every time a row is inserted, without the application needing to calculate or lock anything itself.

The exact mechanism differs by database product, and this is one of the more dialect-specific corners of SQL:

MySQL uses the AUTO_INCREMENT column attribute. The engine stores the current counter value with the table and increments it on every insert.
PostgreSQL traditionally uses the SERIAL pseudo-type (which creates a hidden sequence object), or in modern PostgreSQL the standard GENERATED ALWAYS AS IDENTITY clause.
SQL Server uses the IDENTITY(seed, increment) property on a column.
SQLite (the engine used to verify the examples on this page) is special: any column declared INTEGER PRIMARY KEY automatically becomes an alias for the table’s internal rowid, and SQLite will auto-assign it the next available integer if you omit it from your INSERT. Adding the extra keyword AUTOINCREMENT changes the algorithm slightly (explained below) to guarantee IDs are never reused.

In every dialect, the underlying idea is the same: the column is backed by an internal counter that only the engine touches, and application code should almost never write to it directly.

Without AUTOINCREMENT vs. with AUTOINCREMENT (SQLite specifics)

Because this course’s examples run on SQLite, it’s worth understanding the two ways SQLite generates IDs, since the difference trips people up constantly:

id INTEGER PRIMARY KEY — SQLite picks the next ID as (current maximum rowid in the table) + 1. If the highest-numbered row is later deleted, that ID number can be reused by a future insert.
id INTEGER PRIMARY KEY AUTOINCREMENT — SQLite instead tracks the highest ID ever assigned in a hidden bookkeeping table called sqlite_sequence, and never reuses a number even after deletion. This is closer to how MySQL’s AUTO_INCREMENT and PostgreSQL’s IDENTITY behave, at the cost of a small amount of extra overhead per insert.

Syntax

The general form for creating an auto-incrementing primary key (SQLite syntax, which is what this course executes):

CREATE TABLE table_name (
    column_name INTEGER PRIMARY KEY AUTOINCREMENT,
    other_column TEXT,
    ...
);

Part	Meaning
`INTEGER`	The column’s declared type. It must be exactly `INTEGER` (not `INT`) for SQLite’s rowid-alias behavior to apply.
`PRIMARY KEY`	Marks the column as the table’s unique row identifier.
`AUTOINCREMENT`	Optional keyword that prevents ID reuse after deletes, at a small performance cost. Without it, SQLite still auto-generates IDs, just with the simpler max+1 rule.

The equivalent syntax you’ll see referenced in other databases: MySQL uses id INT AUTO_INCREMENT PRIMARY KEY; PostgreSQL uses id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY or the shorthand id SERIAL PRIMARY KEY; SQL Server uses id INT IDENTITY(1,1) PRIMARY KEY. All four accomplish the same goal — auto-generating a unique, increasing key — with different keywords.

Examples

Example 1: Basic auto-generated IDs

Create a table with an auto-incrementing primary key and insert rows without ever specifying the ID column:

CREATE TABLE employees_demo (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

INSERT INTO employees_demo (name) VALUES ('Alice');
INSERT INTO employees_demo (name) VALUES ('Bob');
INSERT INTO employees_demo (name) VALUES ('Carol');

SELECT * FROM employees_demo;

Result:

id	name
1	Alice
2	Bob
3	Carol

Because id is never supplied in the INSERT statements, SQLite assigns 1, 2, and 3 in insertion order. This is the behavior nearly every dialect shares: leave the auto-increment column out of the column list (or pass DEFAULT/NULL for it in some dialects) and let the engine fill it in.

Example 2: AUTOINCREMENT never reuses a deleted ID

CREATE TABLE orders_demo (
    order_id INTEGER PRIMARY KEY AUTOINCREMENT,
    customer_name TEXT NOT NULL,
    amount REAL
);

INSERT INTO orders_demo (customer_name, amount) VALUES ('John Doe', 250.00);
INSERT INTO orders_demo (customer_name, amount) VALUES ('Jane Smith', 175.50);
DELETE FROM orders_demo WHERE order_id = 2;
INSERT INTO orders_demo (customer_name, amount) VALUES ('Mike Ross', 320.75);

SELECT * FROM orders_demo;

Result:

order_id	customer_name	amount
1	John Doe	250.0
3	Mike Ross	320.75

Jane Smith’s row (order_id 2) was deleted, but the next insert got order_id 3, not the freed-up 2. That’s the whole point of the AUTOINCREMENT keyword: because sqlite_sequence remembers that 2 was already used, it will never be handed out again for this table, even though it’s no longer present in the data.

Example 3: Without AUTOINCREMENT, IDs are simply max+1

CREATE TABLE products_demo (
    product_id INTEGER PRIMARY KEY,
    product_name TEXT NOT NULL
);

INSERT INTO products_demo (product_id, product_name) VALUES (100, 'Widget');
INSERT INTO products_demo (product_name) VALUES ('Gadget');
INSERT INTO products_demo (product_name) VALUES ('Sprocket');

SELECT * FROM products_demo;

Result:

product_id	product_name
100	Widget
101	Gadget
102	Sprocket

Here product_id is a plain INTEGER PRIMARY KEY without AUTOINCREMENT. The first row’s ID (100) was supplied explicitly. Every following row that omits product_id simply gets the current maximum + 1 — 101, then 102 — which is why explicitly-inserted high values can throw off the sequence, as the next section explains.

How It Works Step by Step

When an INSERT statement omits the auto-increment column, the engine performs roughly these steps before the row is written:

1. Determine the candidate ID. Without AUTOINCREMENT, SQLite scans for the current highest rowid in the table and adds 1. With AUTOINCREMENT, it instead reads the last-used value stored in sqlite_sequence and adds 1.
2. Check uniqueness. Because the column is also the primary key, the engine verifies no existing row already has that value (this can only fail if you’ve been manually inserting explicit IDs).
3. Write the row with the generated ID.
4. Update the counter. With AUTOINCREMENT, sqlite_sequence is updated so this value is never reissued, even after a future DELETE.

This is conceptually identical to how MySQL’s AUTO_INCREMENT counter and PostgreSQL’s sequence objects work: a counter lives alongside the table (or as a separate sequence object in PostgreSQL/SQL Server-style identity columns), gets read and incremented atomically on insert, and is exposed to the application afterward via a function like last_insert_rowid() (SQLite), LAST_INSERT_ID() (MySQL), or the RETURNING clause (PostgreSQL).

Common Mistakes

Mistake 1: Using another dialect’s syntax

Copying MySQL syntax directly into SQLite (or vice versa) fails, because AUTO_INCREMENT is not a recognized SQLite keyword combination:

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50)
);

This raises a syntax error in SQLite. The column type must be exactly INTEGER, and the keyword is AUTOINCREMENT (one word, no underscore), placed after PRIMARY KEY:

CREATE TABLE employees_fixed (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT
);

INSERT INTO employees_fixed (name) VALUES ('Dana');

SELECT * FROM employees_fixed;

Result: one row, id = 1, name = 'Dana'. Always check the syntax reference for the specific database you’re targeting rather than assuming keywords are portable — this is one of the least standardized areas of SQL.

Mistake 2: Manually inserting explicit IDs that later collide

Supplying your own value for an auto-increment column works — until a future auto-generated value collides with one you hand-picked earlier:

CREATE TABLE inventory_demo (
    item_id INTEGER PRIMARY KEY AUTOINCREMENT,
    item_name TEXT
);

INSERT INTO inventory_demo (item_id, item_name) VALUES (1, 'Hammer');
INSERT INTO inventory_demo (item_id, item_name) VALUES (1, 'Wrench');

The second insert fails with a unique constraint violation, because item_id 1 is already taken. The fix is simple: let the engine assign the ID by leaving the column out of the insert entirely, rather than trying to manage numbering yourself:

CREATE TABLE inventory_demo2 (
    item_id INTEGER PRIMARY KEY AUTOINCREMENT,
    item_name TEXT
);

INSERT INTO inventory_demo2 (item_name) VALUES ('Hammer');
INSERT INTO inventory_demo2 (item_name) VALUES ('Wrench');

SELECT * FROM inventory_demo2;

Result: (1, 'Hammer') and (2, 'Wrench') — no conflict, because the engine chose both IDs itself.

Best Practices

Never write application logic that guesses the next ID by running SELECT MAX(id) and adding 1 — this races with concurrent inserts and defeats the purpose of auto-increment. Let the database generate it.
After an insert, retrieve the generated ID with the engine’s built-in function (last_insert_rowid() in SQLite, LAST_INSERT_ID() in MySQL, RETURNING id in PostgreSQL) instead of querying for it separately.
Only use SQLite’s AUTOINCREMENT keyword when you specifically need the guarantee that IDs are never reused (for example, if IDs are exposed externally, like in invoice numbers). If you don’t need that guarantee, plain INTEGER PRIMARY KEY is slightly faster.
Treat auto-increment IDs as opaque identifiers, not as a count of rows or a gapless sequence — deletes, rollbacks, and replication can all create gaps.
Don’t rely on auto-increment values to encode meaning (like an ID that resets per year) — use a separate computed column for that instead.
When migrating a table between database products, remember the auto-increment keyword itself does not transfer — translate AUTO_INCREMENT/SERIAL/IDENTITY/AUTOINCREMENT to the target dialect’s equivalent.

Practice Exercises

Exercise 1: Create a table students(student_id INTEGER PRIMARY KEY AUTOINCREMENT, student_name TEXT), insert three students without specifying IDs, then delete the second one and insert a fourth. What ID does the fourth student get, and why?
Exercise 2: Create the same table without the AUTOINCREMENT keyword. Repeat the same insert/delete/insert sequence. Does the fourth student get a different ID than in Exercise 1? Explain the difference in your own words.
Exercise 3: Write a query that retrieves the ID that SQLite assigned to the most recently inserted row, using last_insert_rowid(), immediately after inserting a new student.

Summary

Auto-increment columns let the database generate unique, increasing IDs automatically so applications never have to compute them.
Syntax differs by dialect: AUTO_INCREMENT (MySQL), SERIAL/GENERATED ... AS IDENTITY (PostgreSQL), IDENTITY(1,1) (SQL Server), and SQLite’s INTEGER PRIMARY KEY with optional AUTOINCREMENT.
In SQLite, plain INTEGER PRIMARY KEY reuses IDs after deletion (max+1 rule); adding AUTOINCREMENT guarantees an ID is never reused.
Never manually manage or guess the next ID — let the engine assign it and retrieve it afterward with the dialect’s built-in function.
Copying auto-increment syntax between database products is a common source of errors — always match the syntax to the target engine.

SQL AUTO INCREMENT

Overview: How AUTO INCREMENT Works

Without AUTOINCREMENT vs. with AUTOINCREMENT (SQLite specifics)

Syntax

Examples

Example 1: Basic auto-generated IDs

Example 2: AUTOINCREMENT never reuses a deleted ID

Example 3: Without AUTOINCREMENT, IDs are simply max+1

How It Works Step by Step

Common Mistakes

Mistake 1: Using another dialect’s syntax

Mistake 2: Manually inserting explicit IDs that later collide

Best Practices

Practice Exercises

Summary

Table of Contents