Identity Column
SQL · Working with Tables
Theory
An identity column, also called auto-increment, is a column whose value the database generates for each new row, usually 1, 2, 3 and so on. You insert only the other columns and the engine fills in the next number, which makes it the most common way to create a surrogate primary key.
Syntax differs by engine: standard SQL, PostgreSQL and Oracle use GENERATED ALWAYS AS IDENTITY (or BY DEFAULT); SQL Server uses IDENTITY(1,1); MySQL uses AUTO_INCREMENT; SQLite uses INTEGER PRIMARY KEY; Snowflake uses AUTOINCREMENT or IDENTITY. Numbers are unique but not guaranteed gap-free: a rolled-back insert or a deleted row leaves a gap, and numbers are not reused. With GENERATED ALWAYS, you cannot supply your own value without a special override.
Why we use it
Coming up with unique ids by hand is error-prone, especially when many users insert at the same time. An identity column hands out unique, increasing numbers safely and automatically.
When to use it
Use an identity column for surrogate primary keys on most entity tables. Use a natural key instead when a stable business id already exists, and consider UUIDs when ids must be created in many systems without coordination.
How it works
- INSERT a row without the id
- Ask the identity generator for the next value
- Assign it to the id column
- Does the insert succeed? Yes: Row stored with the new id. No: Value is used up, leaving a gap.
- Next insert gets a higher number
Syntax
column INT GENERATED ALWAYS AS IDENTITYExample
CREATE TABLE employees (
id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name VARCHAR(100)
);Key points
- MySQL uses AUTO_INCREMENT, SQL Server uses IDENTITY(1,1)
- Standard SQL form: GENERATED ALWAYS AS IDENTITY
- Removes the need to manually assign key values
- Values are unique but gaps are normal after rollbacks or deletes
- Do not use identity values to count rows