Identity Columns

Definition

An identity column is a column-level mechanism that automatically generates a unique, sequential value on insert — most commonly used for surrogate primary keys.

sql
-- Standard SQL / PostgreSQL
CREATE TABLE students (
    id   INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

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

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

GENERATED ALWAYS vs GENERATED BY DEFAULT

The standard SQL/PostgreSQL syntax offers two modes:

• GENERATED ALWAYS AS IDENTITY — the engine always assigns the value; an explicit INSERT value is rejected unless you use OVERRIDING SYSTEM VALUE.
• GENERATED BY DEFAULT AS IDENTITY — behaves like a normal default: the engine generates a value only if the insert doesn't supply one, useful when migrating existing rows with pre-assigned IDs.

sql
-- bulk-loading legacy IDs into a GENERATED ALWAYS column requires an explicit override
INSERT INTO students (id, name) OVERRIDING SYSTEM VALUE VALUES (500, 'Legacy Import');

How It Works

Internally, most engines (including PostgreSQL) implement identity columns using a hidden, dedicated sequence object bound one-to-one to that column. Each insert calls the equivalent of nextval() on that internal sequence to obtain the next value, which is then used as the column's default.

Edge Cases and Pitfalls

• Gaps are normal, not bugs: a rolled-back transaction or a failed insert still consumes a value from the counter (because the counter increment itself isn't part of the transaction being rolled back on most engines), so id sequences having gaps (1, 2, 5, 6...) is expected behavior, not corruption.
• TRUNCATE vs DELETE effect on the counter: many engines reset the identity counter on TRUNCATE (Postgres needs the explicit RESTART IDENTITY option; MySQL resets by default) but leave it untouched after a plain DELETE, even DELETE FROM students; with no WHERE — the next inserted row still continues from wherever the counter was.
• Concurrency-safe by design: the mechanism guarantees no two concurrent inserts get the same value, without the application needing its own locking.
• Cannot easily "reuse" a deleted ID for a new row — by design, since gaps are considered acceptable and reuse risks referencing stale foreign keys.

Key Takeaways / Q&A

Q: Identity Column vs Sequence — what's the core distinction? A: An identity column is bound to exactly one column of one table and generates values automatically as part of that column's definition. A Sequence (see next topic) is a standalone, independent object that can be shared across multiple tables/columns and referenced explicitly with nextval(). In fact, PostgreSQL's identity columns are implemented internally using a hidden sequence — identity columns are essentially "a sequence with convenience wrapping" bound to one column.

Q: Why did my table's id jump from 42 straight to 47? A: Almost certainly some inserts between 43 and 46 failed or were rolled back — the counter still advanced even though those rows never landed, which is expected identity-column behavior, not a bug.