AUTO_INCREMENT vs SERIAL vs IDENTITY: Auto-Numbering Compared

Quick comparison table

MySQL: AUTO_INCREMENT

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

AUTO_INCREMENT is the simplest of the four — one keyword on the column definition, and MySQL handles the rest, including tracking the next value internally per table.

PostgreSQL: SERIAL vs GENERATED AS IDENTITY

-- Legacy shorthand — still common, still works
CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name TEXT
);

-- Recommended since PostgreSQL 10
CREATE TABLE customers (
    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name TEXT
);

SERIAL is PostgreSQL-specific sugar that silently creates a separate sequence object behind the scenes — one you can accidentally write to or reset directly, bypassing the table entirely. GENERATED ALWAYS AS IDENTITY is the standard SQL equivalent, ties the sequence more tightly to the column, and by default rejects explicit inserts into that column (use OVERRIDING SYSTEM VALUE if you genuinely need to supply one). New PostgreSQL schemas should prefer it over SERIAL.

SQL Server: IDENTITY

CREATE TABLE customers (
    id INT IDENTITY(1,1) PRIMARY KEY,
    name NVARCHAR(100)
);

The two numbers in IDENTITY(1,1) are the seed (starting value) and the increment (step size) — IDENTITY(1000,5) would start at 1000 and increase by 5 each row. SQL Server allows only one IDENTITY column per table.

Oracle: SEQUENCE and IDENTITY

-- Pre-12c: manual sequence plus trigger
CREATE SEQUENCE customers_seq START WITH 1 INCREMENT BY 1;

CREATE TRIGGER customers_bi
BEFORE INSERT ON customers
FOR EACH ROW
BEGIN
  SELECT customers_seq.NEXTVAL INTO :new.id FROM dual;
END;

-- Oracle 12c+: no trigger needed
CREATE TABLE customers (
    id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name VARCHAR2(100)
);

Before Oracle 12c, auto-numbering required creating a standalone SEQUENCE object and a trigger that pulls the next value into the row on every insert — more moving parts than any other database on this list. Oracle 12c added standard-SQL GENERATED ALWAYS AS IDENTITY, collapsing this down to a single column definition, the same as modern PostgreSQL.

Why gaps in the numbers are normal

A common surprise: insert 10 rows, delete row 5, and the next insert gets 11 — not a renumbered 5. Every one of these mechanisms works the same way underneath: a value is reserved and consumed the moment it's generated, whether or not the surrounding transaction ultimately commits. A rolled-back transaction, a deleted row, or a failed insert due to a constraint violation all burn a number that is never reused.

This is a deliberate design tradeoff, not a defect. Reusing a "wasted" number would require the database to lock the sequence generator across concurrent transactions to check what's actually been committed, which would serialize inserts and destroy write throughput on any table under real concurrent load. Every database on this page accepts gaps as the cost of not doing that.