SQL Reference ยท Data Definition
SQL sequences and auto-increment
Reference for auto-generated IDs: SQLite AUTOINCREMENT, IDENTITY columns, MySQL AUTO_INCREMENT and SEQUENCE objects, with gaps, resets and dialect differences.
Most tables need a unique number for each row, and you rarely want to choose it by hand. Databases generate these numbers for you in three ways: an auto-increment column (MySQL, SQLite), an IDENTITY column (the SQL standard, PostgreSQL, SQL Server, Oracle) or a separate SEQUENCE object that hands out numbers on request.
The generated values are unique and increasing, but not guaranteed to be gap-free. A rolled-back insert or a deleted row leaves a hole that is never refilled.
CREATE TABLE tickets ( id INTEGER PRIMARY KEY AUTOINCREMENT, subject TEXT NOT NULL );
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
-- standard / PostgreSQL / Oracle 12c+ id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY id BIGINT GENERATED BY DEFAULT AS IDENTITY (START WITH 1000 INCREMENT BY 1) -- sequence objects CREATE SEQUENCE order_numbers START WITH 1000 INCREMENT BY 1; NEXT VALUE FOR order_numbers -- standard / SQL Server nextval('order_numbers') -- PostgreSQL order_numbers.NEXTVAL -- Oracle
| Part | Description |
|---|---|
GENERATED ALWAYS |
The database always supplies the value; inserting your own is an error unless overridden. |
GENERATED BY DEFAULT |
The database fills in a value only if you do not give one. |
START WITH / INCREMENT BY |
First value and step. |
CREATE SEQUENCE |
A standalone counter you can share among tables. |
Dialect differences
| Dialect | How to get generated ids |
|---|---|
| SQLite | INTEGER PRIMARY KEY makes the column an alias for the rowid: new rows get max + 1. AUTOINCREMENT additionally guarantees ids are never reused, tracked in sqlite_sequence. No sequences. |
| PostgreSQL | GENERATED ... AS IDENTITY (preferred) or SERIAL/BIGSERIAL (older shortcut); real sequences with nextval(). |
| MySQL | AUTO_INCREMENT on an indexed integer column; LAST_INSERT_ID(). No sequences. |
| SQL Server | IDENTITY(1,1); SCOPE_IDENTITY(); CREATE SEQUENCE since 2012. |
| Oracle | GENERATED AS IDENTITY since 12c; CREATE SEQUENCE before that, usually with a trigger. |
-- MySQL CREATE TABLE tickets (id INT AUTO_INCREMENT PRIMARY KEY, subject VARCHAR(100)); -- SQL Server CREATE TABLE tickets (id INT IDENTITY(1,1) PRIMARY KEY, subject NVARCHAR(100)); -- PostgreSQL INSERT INTO tickets (subject) VALUES ('Login fails') RETURNING id;
Usage notes
- Expect gaps. Rollbacks, deleted rows and cached ranges all leave holes; never rely on ids being consecutive or use them as counts.
- Get the new id from the database, not with
SELECT MAX(id), which races with other writers:last_insert_rowid(),LAST_INSERT_ID(),SCOPE_IDENTITY()orRETURNING. - Overflow: a 32-bit integer runs out at about 2.1 billion; choose
BIGINTfor tables that may grow large. - Exposed ids leak information (how many orders you have). Consider random UUIDs for public identifiers.
- Resetting a counter after deleting rows is dialect-specific, e.g.
DELETE FROM sqlite_sequence WHERE name = 't'in SQLite orTRUNCATE ... RESTART IDENTITYin PostgreSQL.
Related
PRIMARY KEY,CREATE TABLE,TRUNCATE,INSERT,RETURNING- Lessons: Auto increment, Keys