SEQUENCE / AUTOINCREMENT SQL Compatibility | MySQL, PostgreSQL, SQL Server, Oracle, SQLite

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

SQL SEQUENCE / AUTOINCREMENT Compatibility Across Databases
Database System	Support Status	Since Version	Notes
MySQL	Native	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	Native	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	Native	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	Native	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	Native	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: Native since 3.23 PostgreSQL: Native since 6.0 SQL Server: Native since 2000 Oracle: Native since 7 SQLite: Native since 1.0

Per-Database Syntax & Notes

MySQL Native syntax

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 Native syntax

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 Native syntax

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 Native syntax

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 Native syntax

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();