Skip to content
Tutorials Center
My learning

Topics

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.

SQL
CREATE TABLE notes (
  id   INTEGER PRIMARY KEY,
  body TEXT NOT NULL
);

Try it

SQL INTEGER PRIMARY KEY fills itself in ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

SQL AUTOINCREMENT never reuses a number ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
-- 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
SQL
-- 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 KEY is the rowid alias. id INT PRIMARY KEY is an ordinary column and does not fill itself in.
  • Without AUTOINCREMENT SQLite picks the largest existing rowid plus one, so a number freed by deleting the last row can come back. The example above shows plain reusing 3. Add AUTOINCREMENT only 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 BIGINT for busy tables.
  • Copying data between systems that disagree about explicit identity inserts needs extra steps (IDENTITY_INSERT, resetting sequences).