SQL AUTO INCREMENT
Auto-increment columns generate the next integer for you on every INSERT. Perfect for surrogate primary keys.
Per-vendor syntax
| DB | Syntax |
|---|---|
| MySQL / MariaDB | id INT PRIMARY KEY AUTO_INCREMENT |
| PostgreSQL — modern | id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY |
| PostgreSQL — legacy | id SERIAL PRIMARY KEY |
| SQL Server | id INT PRIMARY KEY IDENTITY(1,1) |
| SQLite | id INTEGER PRIMARY KEY AUTOINCREMENT |
| Oracle 12c+ | id NUMBER GENERATED ALWAYS AS IDENTITY |
Inserting without specifying the id
SQL
INSERT INTO customers (name, email)
VALUES ('Ada', 'ada@example.com');
Reading back the new id
| DB | Function |
|---|---|
| MySQL | SELECT LAST_INSERT_ID(); |
| PostgreSQL | INSERT … RETURNING id; |
| SQL Server | SELECT SCOPE_IDENTITY(); or OUTPUT INSERTED.id |
| SQLite | SELECT last_insert_rowid(); |
Gaps in the sequence are normal
Auto-increment values aren't recycled after a delete, and a rolled-back transaction "burns" the value it claimed. Expect 1, 2, 4, 5, 8, … — that's by design.
Tip: Use
BIGINT for auto-increment in new schemas. INT tops out at 2.1 billion; BIGINT is effectively infinite.Example
Example
CREATE TABLE customers ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(80) );Try it Yourself »
Exercise
MySQL syntax for auto-incrementing IDs.
id INT PRIMARY KEY
Two words joined with an underscore.
Discussion
Loading…