SEQUENCE / AUTOINCREMENTDDL
Buy Me A Coffee

Mechanisms for auto-generating unique sequential integer values, typically used for surrogate primary keys. Syntax and feature names vary significantly across engines.

Automatically generates unique numeric IDs on insert: AUTO_INCREMENT (MySQL), SERIAL (PostgreSQL), IDENTITY (SQL Server), AUTOINCREMENT (SQLite). SERIAL creates an underlying sequence object. Sequences allow pre-fetching IDs for bulk inserts.

Compatibility

Show:
Database System Support Status Since Version Notes
MySQL ✓ Supported 3.23 AUTO_INCREMENT column attribute on INTEGER or BIGINT columns is the standard mechanism. Only one AUTO_INCREMENT column per table, and it must be indexed (usually the PRIMARY KEY). The last inserted ID is retrievable with LAST_INSERT_ID(). CREATE SEQUENCE is not supported in MySQL (it is available in MariaDB). Starting value and increment step can be set at table or server level.
PostgreSQL ✓ Supported 6.0 Three mechanisms available: SERIAL/BIGSERIAL (shorthand macro, pre-10, still common); GENERATED ALWAYS AS IDENTITY and GENERATED BY DEFAULT AS IDENTITY (SQL standard, PostgreSQL 10+, preferred for new code); CREATE SEQUENCE for standalone sequence objects. SERIAL is syntactic sugar that creates a sequence and sets the column default — it is NOT a true identity column. GENERATED ALWAYS prevents manual inserts into the column; GENERATED BY DEFAULT allows overriding with an explicit value.
SQL Server ✓ Supported 2000 IDENTITY(seed, increment) column property is the traditional mechanism, available since SQL Server 2000. A standalone SEQUENCE object (standard SQL) was added in SQL Server 2012. IDENTITY values can be retrieved with SCOPE_IDENTITY() (preferred), @@IDENTITY, or the OUTPUT clause. SET IDENTITY_INSERT ON/OFF allows overriding IDENTITY values when needed.
Oracle ✓ Supported 7 CREATE SEQUENCE has been available since Oracle 7. GENERATED AS IDENTITY (standard SQL) was added in Oracle 12c. Before 12c, the pattern was: create a sequence, then use a BEFORE INSERT trigger or include seq.NEXTVAL in the INSERT statement. In 12c+, identity columns are the modern approach.
SQLite ✓ Supported 1.0 INTEGER PRIMARY KEY is an alias for the rowid and auto-increments by default (SQLite chooses one more than the largest existing rowid). The AUTOINCREMENT keyword adds the additional guarantee that rowid values are never reused — this has a minor performance cost and is rarely needed. CREATE SEQUENCE is not supported.

Details

The SQL standard term is GENERATED AS IDENTITY, supported in PostgreSQL 10+, SQL Server 2012+, and Oracle 12c+ — prefer it for new portable schemas. MySQL's AUTO_INCREMENT and SQL Server's IDENTITY(seed,increment) are engine-specific and will not work elsewhere. SQLite's INTEGER PRIMARY KEY / rowid aliasing is unique: it's automatic and requires no keyword unless you want the strict AUTOINCREMENT no-reuse guarantee. A standalone CREATE SEQUENCE object is supported in PostgreSQL, SQL Server (2012+), and Oracle — but not in MySQL or SQLite.

Standard Syntax

-- Standard SQL (PostgreSQL 10+, SQL Server 2012+, Oracle 12c+): CREATE TABLE orders ( id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, amount DECIMAL(10,2) ); -- MySQL: CREATE TABLE orders ( id INT AUTO_INCREMENT PRIMARY KEY, amount DECIMAL(10,2) );

Version Support

MySQL: Since 3.23 PostgreSQL: Since 6.0 SQL Server: Since 2000 Oracle: Since 7 SQLite: Since 1.0

Per-Database Syntax & Notes

MySQL

AUTO_INCREMENT column attribute on INTEGER or BIGINT columns is the standard mechanism. Only one AUTO_INCREMENT column per table, and it must be indexed (usually the PRIMARY KEY). The last inserted ID is retrievable with LAST_INSERT_ID(). CREATE SEQUENCE is not supported in MySQL (it is available in MariaDB). Starting value and increment step can be set at table or server level.

-- Basic AUTO_INCREMENT: CREATE TABLE orders ( id INT AUTO_INCREMENT PRIMARY KEY, total DECIMAL(10,2) ); -- Custom start value: CREATE TABLE orders ( id INT AUTO_INCREMENT PRIMARY KEY, total DECIMAL(10,2) ) AUTO_INCREMENT = 1000; -- Reset/set current value: ALTER TABLE orders AUTO_INCREMENT = 5000; -- Get last inserted ID: SELECT LAST_INSERT_ID();

PostgreSQL

Three mechanisms available: SERIAL/BIGSERIAL (shorthand macro, pre-10, still common); GENERATED ALWAYS AS IDENTITY and GENERATED BY DEFAULT AS IDENTITY (SQL standard, PostgreSQL 10+, preferred for new code); CREATE SEQUENCE for standalone sequence objects. SERIAL is syntactic sugar that creates a sequence and sets the column default — it is NOT a true identity column. GENERATED ALWAYS prevents manual inserts into the column; GENERATED BY DEFAULT allows overriding with an explicit value.

-- SQL standard (recommended, PG 10+): CREATE TABLE orders ( id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, total DECIMAL(10,2) ); -- GENERATED BY DEFAULT (allows override): CREATE TABLE orders ( id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, total DECIMAL(10,2) ); -- Legacy SERIAL (still common): CREATE TABLE orders ( id SERIAL PRIMARY KEY, total DECIMAL(10,2) ); -- Standalone sequence: CREATE SEQUENCE order_id_seq START 1000 INCREMENT 1; SELECT nextval('order_id_seq'); SELECT currval('order_id_seq');

SQL Server

IDENTITY(seed, increment) column property is the traditional mechanism, available since SQL Server 2000. A standalone SEQUENCE object (standard SQL) was added in SQL Server 2012. IDENTITY values can be retrieved with SCOPE_IDENTITY() (preferred), @@IDENTITY, or the OUTPUT clause. SET IDENTITY_INSERT ON/OFF allows overriding IDENTITY values when needed.

-- IDENTITY column: CREATE TABLE orders ( id INT IDENTITY(1,1) PRIMARY KEY, total DECIMAL(10,2) ); -- Get last inserted ID: SELECT SCOPE_IDENTITY() AS new_id; -- OUTPUT clause alternative: INSERT INTO orders (total) OUTPUT INSERTED.id VALUES (99.99); -- Standalone SEQUENCE (2012+): CREATE SEQUENCE order_seq START WITH 1000 INCREMENT BY 1; SELECT NEXT VALUE FOR order_seq;

Oracle

CREATE SEQUENCE has been available since Oracle 7. GENERATED AS IDENTITY (standard SQL) was added in Oracle 12c. Before 12c, the pattern was: create a sequence, then use a BEFORE INSERT trigger or include seq.NEXTVAL in the INSERT statement. In 12c+, identity columns are the modern approach.

-- Standard identity column (12c+): CREATE TABLE orders ( id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, total NUMBER(10,2) ); -- GENERATED BY DEFAULT (allows override): CREATE TABLE orders ( id NUMBER GENERATED BY DEFAULT AS IDENTITY START WITH 1000 PRIMARY KEY, total NUMBER(10,2) ); -- Legacy sequence approach (pre-12c): CREATE SEQUENCE order_id_seq START WITH 1 INCREMENT BY 1 NOCACHE; INSERT INTO orders (id, total) VALUES (order_id_seq.NEXTVAL, 99.99); SELECT order_id_seq.CURRVAL FROM dual;

SQLite

INTEGER PRIMARY KEY is an alias for the rowid and auto-increments by default (SQLite chooses one more than the largest existing rowid). The AUTOINCREMENT keyword adds the additional guarantee that rowid values are never reused — this has a minor performance cost and is rarely needed. CREATE SEQUENCE is not supported.

-- INTEGER PRIMARY KEY (implicit autoincrement, recommended): CREATE TABLE orders ( id INTEGER PRIMARY KEY, total REAL ); -- AUTOINCREMENT keyword (strict no-reuse guarantee): CREATE TABLE orders ( id INTEGER PRIMARY KEY AUTOINCREMENT, total REAL ); -- Get last inserted rowid: SELECT last_insert_rowid();