SQL / DEFINING TABLES AND CONSTRAINTS
Identity columns and auto-generated keys
Declare identity columns so the database generates keys for you, read the new value back, and predict when a hand-inserted id will collide with the counter.
What you will learn
- Declare bigint GENERATED ALWAYS AS IDENTITY and stop sending the key from app code
- Choose BY DEFAULT only when you must load rows that already carry their own ids
- Read the generated key with RETURNING instead of a follow-up SELECT max(id)
- Restart the identity above the highest manual id to avoid a later duplicate key
Understanding Identity columns and auto-generated keys
An identity column is a column whose value comes from a counter object that the database owns and keeps beside the table. You declare it as invoice_id bigint GENERATED ALWAYS AS IDENTITY, then leave the column out of your INSERT statements and the server fills it in. Every mainstream engine has this idea under a different spelling: PostgreSQL's older serial, MySQL's AUTO_INCREMENT, SQL Server's IDENTITY(1,1), SQLite's bare INTEGER PRIMARY KEY. The mental model that saves you later is that the counter is a separate object next to the table, not a fact computed from the rows inside it.
Because it is separate, the counter never looks at the data. It hands out the next number and moves on, so it does not roll back when your transaction does, it does not notice ids you typed in yourself, and it never returns to fill the holes it leaves. That is a deliberate trade: a generator that promised gapless numbering would have to make concurrent inserters queue up and wait for each other to commit. A jump from 41 to 43 therefore means an insert failed or was rolled back, not that a row was deleted.
GENERATED ALWAYS makes the server reject any INSERT that names the column unless that one statement says OVERRIDING SYSTEM VALUE; GENERATED BY DEFAULT accepts whatever you pass without comment. Use ALWAYS for ordinary tables and keep BY DEFAULT for migrations and bulk loads that arrive with their own ids, then restart the counter above them. Since you are no longer supplying the key, you have to ask for it back: RETURNING in PostgreSQL, OUTPUT in SQL Server, LAST_INSERT_ID() in MySQL, getGeneratedKeys() through JDBC. Note also that the identity clause only produces values; the PRIMARY KEY declaration next to it is the part that actually refuses duplicates.
CREATE TABLE invoice (
invoice_id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
customer text NOT NULL,
amount numeric(8,2) NOT NULL
);
INSERT INTO invoice (customer, amount) VALUES ('Ada', 120.00), ('Grace', 75.50);
-- BY DEFAULT lets the client win, and the counter is never told
INSERT INTO invoice (invoice_id, customer, amount) VALUES (50, 'Linus', 10.00);
INSERT INTO invoice (customer, amount) VALUES ('Ken', 33.25);
SELECT invoice_id, customer, amount FROM invoice ORDER BY invoice_id;An identity column is an independent counter the database owns, not a value derived from the rows already in the table.
Worked examples
Who owns the value: ALWAYS and its escape hatch
Shows that GENERATED ALWAYS refuses a client-supplied key outright, and that the override is per statement.
CREATE TABLE ticket (
ticket_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
subject text NOT NULL
);
INSERT INTO ticket (subject) VALUES ('printer jam');
INSERT INTO ticket (ticket_id, subject) VALUES (99, 'lost badge');
INSERT INTO ticket (ticket_id, subject) OVERRIDING SYSTEM VALUE VALUES (99, 'lost badge');
SELECT * FROM ticket ORDER BY ticket_id;Example explained
Line 1The first INSERT omits ticket_id, so the generator supplies 1.
Line 2The second INSERT names ticket_id, and ALWAYS makes the server reject the whole statement rather than silently ignore the 99.
Line 3OVERRIDING SYSTEM VALUE succeeds, which proves the guard is about intent, not about capability.
Line 4That override did not advance the counter, so the next generated id is still 2 while 99 already sits in the table.
Reading the new key back, and the gap a failure leaves
Uses RETURNING to capture the generated key and shows that a failed insert consumes a number permanently.
CREATE TABLE author (
author_id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
name text NOT NULL UNIQUE
);
INSERT INTO author (name) VALUES ('Borges') RETURNING author_id;
INSERT INTO author (name) VALUES ('Borges');
INSERT INTO author (name) VALUES ('Calvino') RETURNING author_id;Example explained
Line 1RETURNING author_id sends the generated value back in the same round trip, so no second query is needed to learn the key.
Line 2The duplicate-name insert draws 2 from the counter before the UNIQUE check runs, then fails.
Line 3Calvino gets 3 because the counter is outside transaction control, which is exactly what lets two sessions insert at once without waiting.
Line 4The missing 2 is normal operation, not evidence that a row was deleted.
Important notes
Identity applies only to integer types, and an integer identity stops at 2147483647; a table with heavy insert and delete churn can exhaust that while holding very few rows.
The identity clause supplies values but enforces nothing. The PRIMARY KEY or UNIQUE constraint is what rejects a duplicate, which matters as soon as BY DEFAULT lets a caller pass its own id.
Common mistakes
Seeding a BY DEFAULT identity table with explicit ids and leaving the counter at 1; inserts work for months, then fail with a duplicate key error the moment the counter reaches the seeded range.
Using the id as a gapless business number such as an invoice number; one rolled-back transaction burns its value forever and the numbering looks like records went missing.
Computing the key yourself with SELECT max(id) + 1; two concurrent sessions read the same maximum and one insert dies on the primary key, which happens constantly under load.
Try it yourself
Change, predict, then run
Create a table with id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, insert two rows normally, insert a third with an explicit id of 10, then insert one more normally and confirm it receives 4 rather than 11. Run ALTER TABLE ... ALTER COLUMN id RESTART WITH 11 and check that the next insert lands on 11.
Open the SQL workspaceCheck your understanding
A table has id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY. Rows 1 and 2 were inserted normally, then one row was inserted with an explicit id of 500. What happens on the next insert that omits id?
- It gets 3, and inserts will start failing with a duplicate key error once the counter eventually reaches 500
- It gets 501, because the identity generator tracks the largest value currently in the column
- The insert fails, because the generator detects that an out-of-range id was inserted manually
- It gets 3, and the generator will later skip 500 because the primary key already holds that value
Show answer
The generator is an independent counter that was never told about the manual insert, so it continues at 3 and will one day offer 500, which the primary key then rejects. Option 1 is tempting because some engines behave that way in places (InnoDB recomputes AUTO_INCREMENT from the maximum value when it opens a table), but a standard identity generator never reads the column. Option 3 fails for the same reason: nothing lets the counter see which values are taken.