SQL Reference ยท Dialects and Concepts
SQL auto-increment across dialects
How each database generates primary key values: SQLite rowid and AUTOINCREMENT, PostgreSQL identity and serial, MySQL AUTO_INCREMENT, SQL Server IDENTITY, Oracle.
Most tables need a unique key that the database creates for you. Every system can do this, but the syntax differs. The standard way is an identity column: GENERATED ... AS IDENTITY. PostgreSQL and Oracle support it, MySQL uses AUTO_INCREMENT, SQL Server uses IDENTITY(seed, step), and SQLite quietly turns an INTEGER PRIMARY KEY column into an alias of its internal row number.
Generated numbers are unique, not gap-free. A rolled-back insert or a failed statement still consumes a number in most systems, so never rely on keys being consecutive.
CREATE TABLE notes ( id INTEGER PRIMARY KEY, body 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
-- SQLite id INTEGER PRIMARY KEY -- becomes an alias of rowid id INTEGER PRIMARY KEY AUTOINCREMENT -- never reuses an id -- PostgreSQL 10+ and Oracle 12c+ (standard) id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY -- PostgreSQL (older style) id SERIAL PRIMARY KEY -- or BIGSERIAL -- MySQL, MariaDB id INT AUTO_INCREMENT PRIMARY KEY -- SQL Server id INT IDENTITY(1, 1) PRIMARY KEY
| Option | Description |
|---|---|
GENERATED ALWAYS |
The database always supplies the value; supplying your own is an error unless you override it. |
GENERATED BY DEFAULT |
The database supplies a value only when you leave the column out. |
IDENTITY(seed, step) |
SQL Server: first value and increment. |
AUTOINCREMENT |
SQLite: keeps a high-water mark so numbers are never reused. Rarely needed. |
Dialect differences
| Topic | SQLite | PostgreSQL | MySQL | SQL Server | Oracle |
|---|---|---|---|---|---|
| Declaration | INTEGER PRIMARY KEY |
GENERATED ... AS IDENTITY or SERIAL |
AUTO_INCREMENT |
IDENTITY(1,1) |
GENERATED ... AS IDENTITY (12c+); sequences + triggers before |
| Separate sequence object | no | yes, CREATE SEQUENCE |
no | yes, since 2012 | yes |
| Read the new value | last_insert_rowid() or RETURNING id |
RETURNING id |
LAST_INSERT_ID() |
SCOPE_IDENTITY() or OUTPUT |
RETURNING id INTO :var |
| Insert an explicit value | allowed | BY DEFAULT: yes; ALWAYS: error |
allowed | needs SET IDENTITY_INSERT ON |
BY DEFAULT: yes |
| Gaps after rollback | a rollback undoes the number, but deletes leave gaps | yes | yes (InnoDB) | yes | yes |
-- PostgreSQL: read the generated key back INSERT INTO notes (body) VALUES ('hello') RETURNING id; -- MySQL INSERT INTO notes (body) VALUES ('hello'); SELECT LAST_INSERT_ID(); -- SQL Server INSERT INTO notes (body) VALUES ('hello'); SELECT SCOPE_IDENTITY(); -- Oracle 12c+ CREATE TABLE notes ( id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, body VARCHAR2(200) );
A different approach is a random key such as a UUID (gen_random_uuid() in PostgreSQL, NEWID() in SQL Server, UUID() in MySQL). It avoids a central counter and hides how many rows exist, at the cost of bigger indexes and less ordered inserts.
Usage notes
- In SQLite, the exact spelling matters.
id INTEGER PRIMARY KEYis the rowid alias.id INT PRIMARY KEYis an ordinary column and does not fill itself in. - Without
AUTOINCREMENTSQLite picks the largest existing rowid plus one, so a number freed by deleting the last row can come back. The example above showsplainreusing 3. AddAUTOINCREMENTonly if reuse would be harmful. - Do not guess the next id with
SELECT MAX(id) + 1. Two concurrent sessions get the same number. Let the database generate it and read it back. - Gaps are normal. Never build business meaning (invoice numbers without gaps) on identity values. Use a separate counter table with locking if the law requires gap-free numbers.
- Pick a wide enough type. A 32-bit integer runs out at about 2.1 billion; use
BIGINTfor busy tables. - Copying data between systems that disagree about explicit identity inserts needs extra steps (
IDENTITY_INSERT, resetting sequences).