SQL - Sequences & Identity
Overview
MySQL uses AUTO_INCREMENT, SQL Server supports IDENTITY and SEQUENCE, and other engines offer CREATE SEQUENCE with NEXTVAL/CURRVAL semantics. SQLite does not support CREATE SEQUENCE; use INTEGER PRIMARY KEY AUTOINCREMENT and last_insert_rowid().
-- AUTO_INCREMENT on a column
CREATE TABLE t (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50)
);
-- Identity and Sequence
CREATE TABLE t (
id INT IDENTITY(1,1) PRIMARY KEY,
name NVARCHAR(50)
);
-- Sequence example
CREATE SEQUENCE MySeq START WITH 1 INCREMENT BY 1;
SELECT NEXT VALUE FOR MySeq;
-- No sequences; use INTEGER PRIMARY KEY (optionally AUTOINCREMENT)
CREATE TABLE t (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT
);
See "Auto Increment / Identity" for a live demo with last_insert_rowid().