SQL AUTO_INCREMENT Explained

Almost every table needs a unique identifier for each row, and almost nobody wants to generate those ids by hand. AUTO_INCREMENT (and its equivalents in other databases) tells the database to automatically assign the next sequential number every time you insert a new row, so you never have to track "what's the next id?" yourself.

This is especially useful on a primary key column — you almost always want new rows to get a fresh, guaranteed-unique id without any extra work.

AUTO_INCREMENT Syntax Differs by Database

This is one of the few places where SQL syntax genuinely diverges between database systems, so it's worth knowing all three:

-- MySQL
CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100)
);

-- PostgreSQL
CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100)
);

-- SQL Server
CREATE TABLE customers (
    id INT IDENTITY(1,1) PRIMARY KEY,
    name VARCHAR(100)
);

All three do the same thing: the first row inserted gets id 1, the next gets 2, and so on — automatically, without you ever specifying a value.

Inserting Without Specifying the ID

Once a column is set up this way, you simply omit it from your INSERT statement and the database fills it in:

INSERT INTO customers (name) VALUES ('Meera Nair');
INSERT INTO customers (name) VALUES ('James Carter');

SELECT * FROM customers;

Result:

id | name
---+--------------
1 | Meera Nair
2 | James Carter

Why You Almost Always Want This on a Primary Key

Manually managing ids is a common source of bugs — two processes inserting at the same time could easily pick the same "next" id if you're calculating it yourself with something like SELECT MAX(id) + 1. Letting the database own id generation avoids that whole class of race condition, and it's one less thing your application code has to think about when it runs an INSERT INTO statement.

---