SQL Auto Increment

An Auto-Increment field automatically generates a unique numeric value whenever a new record is inserted into a table. It is most commonly used to automatically create Primary Key values (ID columns) without requiring manual entry or application-side sequence management.

While auto-increment is a standard SQL concept, the syntax varies significantly across major database systems.

1. Syntax by Database Engine

MySQL

MySQL uses the AUTO_INCREMENT keyword. By default, it starts at 1 and increments by 1.

CREATE TABLE Users (
    UserID INT AUTO_INCREMENT PRIMARY KEY,
    Username VARCHAR(50) NOT NULL,
    Email VARCHAR(100)
);

-- Change the starting value (optional)
ALTER TABLE Users AUTO_INCREMENT = 1000;

SQL Server (T-SQL)

SQL Server uses the IDENTITY(seed, increment) function:

• seed: The starting value (default 1).
• increment: The step value added to each row (default 1).

CREATE TABLE Users (
    UserID INT IDENTITY(1,1) PRIMARY KEY,
    Username VARCHAR(50) NOT NULL,
    Email VARCHAR(100)
);

Note: To manually insert a specific value into an IDENTITY column, run SET IDENTITY_INSERT Users ON; before executing your INSERT statement.

PostgreSQL

PostgreSQL supports two approaches: standard ANSI GENERATED AS IDENTITY (recommended) or the legacy SERIAL data type.

Recommended: ANSI Standard Identity Column

CREATE TABLE Users (
    UserID INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    Username VARCHAR(50) NOT NULL,
    Email VARCHAR(100)
);

• ALWAYS: Prevents manual values from being inserted unless OVERRIDING SYSTEM VALUE is specified.
• BY DEFAULT: Generates automatic values but allows manual overrides without special syntax.

Legacy: SERIAL Data Type

CREATE TABLE Users (
    UserID SERIAL PRIMARY KEY,
    Username VARCHAR(50) NOT NULL,
    Email VARCHAR(100)
);

MS Access

MS Access uses the AUTOINCREMENT keyword (or selecting the AutoNumber data type in the table designer).

CREATE TABLE Users (
    UserID AUTOINCREMENT PRIMARY KEY,
    Username VARCHAR(50) NOT NULL,
    Email VARCHAR(100)
);

Oracle SQL

Modern Oracle versions (12c and newer) support standard ANSI identity columns. Older versions rely on manual SEQUENCE and TRIGGER objects.

-- Oracle 12c+ Syntax
CREATE TABLE Users (
    UserID NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    Username VARCHAR2(50) NOT NULL,
    Email VARCHAR2(100)
);

2. Inserting Records into Auto-Increment Tables

When inserting data into a table with an auto-incrementing key, omit the primary key column from your INSERT INTO statement. The database engine will automatically generate and assign the next sequential value.

-- Inserting rows without explicitly supplying UserID
INSERT INTO Users (Username, Email)
VALUES ('john_doe', 'john@example.com'),
       ('jane_smith', 'jane@example.com');

3. How to Retrieve the Last Inserted ID

When creating parent-child relationships (e.g., placing an Order and creating Order Details), you often need the generated primary key value immediately after inserting a record.

Quick Comparison Summary