Skip to content
Tutorials Center
My learning

Topics

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.

SQL
CREATE TABLE tickets (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  subject TEXT NOT NULL
);

Try it

SQL Auto-generated ids in SQLite

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

SQL INTEGER PRIMARY KEY vs AUTOINCREMENT

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

Syntax

SQL
-- 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.
SQL
-- 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() or RETURNING.
  • Overflow: a 32-bit integer runs out at about 2.1 billion; choose BIGINT for 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 or TRUNCATE ... RESTART IDENTITY in PostgreSQL.