Identity, Sequences and Generated Columns

## The problem an identity column solves Most tables need a surrogate key: a value with no business meaning whose only job is to identify the row. Nobody wants the application to compute it, because two concurrent sessions running `SELECT MAX(id) + 1` will hand out the same number. The database is the only place that can mint a unique number under concurrency, so SQL gives you a declaration that says "this column's values come from the engine". ## The ANSI syntax ```sql column_name data_type GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ] ``` A complete table: ```sql CREATE TABLE invoices ( invoice_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, customer_id BIGINT NOT NULL, total NUMERIC(12,2) NOT NULL ); INSERT INTO invoices (customer_id, total) VALUES (42, 99.50); ``` The INSERT simply omits `invoice_id`; the engine fills it. The column type must be exact numeric with scale 0 — `SMALLINT`, `INTEGER`, `BIGINT`, or `NUMERIC(p,0)`. Picking `INTEGER` caps you near 2.1 billion values, which is why `BIGINT` is the common default for anything that might grow. ## The sequence options The parenthesised list is the same option set `CREATE SEQUENCE` accepts: ```sql ticket_no BIGINT GENERATED ALWAYS AS IDENTITY (START WITH 1000 INCREMENT BY 1 MINVALUE 1000 NO MAXVALUE NO CYCLE) ``` - `START WITH n` — the first value handed out. - `INCREMENT BY n` — the step; a negative step counts down. - `MINVALUE` / `MAXVALUE` (or `NO MINVALUE` / `NO MAXVALUE`) — the bounds. - `CYCLE` / `NO CYCLE` — whether the generator wraps around at the bound or raises an error. `NO CYCLE` is the sane choice for a key: cycling would eventually collide with the primary key constraint. Omit the parentheses entirely and you get the engine's defaults, which start at 1 and step by 1. ## What the declaration implies An identity column is implicitly `NOT NULL` — you do not need to write it, and you cannot make it nullable. The standard permits at most **one** identity column per table, which is a deliberate restriction: identity is meant for the surrogate key, not for arbitrary counters. And `PRIMARY KEY` is a *separate* declaration. `GENERATED ALWAYS AS IDENTITY` guarantees only that values arrive from a generator; it is the `PRIMARY KEY` or `UNIQUE` constraint that guarantees no duplicate ever lands in the column, which matters as soon as someone loads explicit values into it. ## ALWAYS versus BY DEFAULT, in one line `GENERATED ALWAYS` rejects an INSERT that supplies its own value for the column (unless the INSERT says `OVERRIDING SYSTEM VALUE`). `GENERATED BY DEFAULT` accepts a supplied value and only generates one when the column is omitted or given `DEFAULT`. Choose `ALWAYS` when nothing outside the database should ever choose an id; choose `BY DEFAULT` when a data load legitimately carries its own keys. ## Vendor spellings The idea is universal, the syntax is not: - MySQL: `id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY` - SQL Server: `id BIGINT IDENTITY(1,1) PRIMARY KEY` — the two arguments are seed and increment - SQLite: `id INTEGER PRIMARY KEY` is an alias for the rowid and auto-numbers; the extra `AUTOINCREMENT` keyword changes reuse behaviour - PostgreSQL and Oracle accept the ANSI `GENERATED … AS IDENTITY` form; PostgreSQL also carries the older `serial`/`bigserial` shorthand, which expands to a column with a `DEFAULT` drawing from a separate sequence object The practical consequence is that a schema written in ANSI form may still need one edit per engine, and that a column written as `serial` behaves like `BY DEFAULT` (it is only a default, easily overridden), not like `ALWAYS`. ## Common mistakes Declaring `INTEGER` for a high-volume table and running out of range years later; assuming the generated values are gap-free and contiguous, which no engine promises; assuming the identity declaration alone prevents duplicates when nothing enforces uniqueness; and adding a second identity column to a table, which the standard does not allow.