SQL - AUTO_INCREMENT / IDENTITY / SERIAL

Key Insights

Auto-incrementing columns differ significantly across database systems: MySQL uses AUTO_INCREMENT, PostgreSQL uses SERIAL or IDENTITY, SQL Server and Oracle use IDENTITY, each with distinct syntax and behavior
Understanding sequence management is critical—PostgreSQL’s SERIAL creates an implicit sequence while IDENTITY provides more control, and manual sequence manipulation can cause primary key conflicts
Production scenarios require careful handling of identity column resets, gaps in sequences, and proper synchronization when importing data or merging databases

Understanding Auto-Increment Mechanisms

Auto-incrementing columns generate unique numeric values automatically for each new row. While conceptually simple, implementation varies dramatically across database systems. The underlying mechanism typically involves a sequence generator that maintains the next available value independently of the table data.

MySQL’s AUTO_INCREMENT is table-scoped, meaning each table maintains its own counter. PostgreSQL’s SERIAL types create sequences as separate database objects. SQL Server’s IDENTITY property is column-specific with configurable seed and increment values. Oracle traditionally used sequences explicitly but added IDENTITY columns in 12c.

MySQL AUTO_INCREMENT

MySQL implements auto-increment as a column attribute. The most common pattern uses it with primary keys:

CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO users (username, email) 
VALUES ('alice', 'alice@example.com');

INSERT INTO users (username, email) 
VALUES ('bob', 'bob@example.com');

SELECT * FROM users;
-- user_id: 1, username: alice
-- user_id: 2, username: bob

To start from a specific value or retrieve the last inserted ID:

-- Set starting value
ALTER TABLE users AUTO_INCREMENT = 1000;

-- Get last inserted ID in current session
INSERT INTO users (username, email) 
VALUES ('charlie', 'charlie@example.com');

SELECT LAST_INSERT_ID();
-- Returns: 1000

MySQL’s AUTO_INCREMENT only works with integer types (TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT). The counter persists in the table metadata, but deleting all rows doesn’t reset it unless you explicitly truncate:

DELETE FROM users WHERE user_id >= 1000;
-- AUTO_INCREMENT counter unchanged

TRUNCATE TABLE users;
-- AUTO_INCREMENT reset to 1

PostgreSQL SERIAL and IDENTITY

PostgreSQL offers two approaches: the traditional SERIAL pseudo-type and the SQL-standard IDENTITY column.

SERIAL Types

SERIAL creates an integer column with a default value from an automatically created sequence:

CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2)
);

-- Equivalent to:
CREATE SEQUENCE products_product_id_seq;
CREATE TABLE products (
    product_id INTEGER NOT NULL DEFAULT nextval('products_product_id_seq'),
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2),
    PRIMARY KEY (product_id)
);
ALTER SEQUENCE products_product_id_seq OWNED BY products.product_id;

Inserting data works identically to MySQL:

INSERT INTO products (product_name, price) 
VALUES ('Widget', 19.99);

INSERT INTO products (product_name, price) 
VALUES ('Gadget', 29.99);

-- Get last inserted value
SELECT currval('products_product_id_seq');

PostgreSQL provides SERIAL (4-byte integer), BIGSERIAL (8-byte), and SMALLSERIAL (2-byte) variants.

IDENTITY Columns

PostgreSQL 10+ supports SQL-standard IDENTITY columns with better control:

CREATE TABLE orders (
    order_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    order_date DATE DEFAULT CURRENT_DATE
);

-- Or with explicit configuration
CREATE TABLE invoices (
    invoice_id INTEGER GENERATED ALWAYS AS IDENTITY 
        (START WITH 1000 INCREMENT BY 1) PRIMARY KEY,
    invoice_date DATE NOT NULL
);

GENERATED ALWAYS prevents manual insertion, while GENERATED BY DEFAULT allows it:

CREATE TABLE logs (
    log_id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    message TEXT
);

-- This works with BY DEFAULT
INSERT INTO logs (log_id, message) VALUES (9999, 'Manual ID');

-- This fails with ALWAYS
INSERT INTO orders (order_id, customer_id) VALUES (500, 1);
-- ERROR: cannot insert into column "order_id"

Managing identity columns:

-- Restart sequence
ALTER TABLE orders ALTER COLUMN order_id RESTART WITH 5000;

-- Get sequence information
SELECT pg_get_serial_sequence('orders', 'order_id');

-- Manually set next value
SELECT setval('orders_order_id_seq', 10000);

SQL Server IDENTITY

SQL Server uses IDENTITY(seed, increment) syntax:

CREATE TABLE customers (
    customer_id INT IDENTITY(1,1) PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL,
    registration_date DATETIME DEFAULT GETDATE()
);

-- Start at 1000, increment by 5
CREATE TABLE batch_jobs (
    job_id INT IDENTITY(1000, 5) PRIMARY KEY,
    job_name VARCHAR(50) NOT NULL
);

INSERT INTO batch_jobs (job_name) VALUES ('Nightly Backup');
INSERT INTO batch_jobs (job_name) VALUES ('Data Sync');
-- job_id values: 1000, 1005

Retrieve the last identity value:

INSERT INTO customers (customer_name) VALUES ('Acme Corp');

-- Session-specific last identity
SELECT SCOPE_IDENTITY();

-- Last identity regardless of scope
SELECT @@IDENTITY;

-- Last identity for specific table
SELECT IDENT_CURRENT('customers');

Enable explicit identity insertion:

SET IDENTITY_INSERT customers ON;

INSERT INTO customers (customer_id, customer_name) 
VALUES (500, 'Special Customer');

SET IDENTITY_INSERT customers OFF;

Reseed the identity counter:

-- Set next value to 2000
DBCC CHECKIDENT ('customers', RESEED, 1999);

-- Reset based on current max value
DBCC CHECKIDENT ('customers', RESEED);

Oracle IDENTITY Columns

Oracle 12c introduced identity columns, though sequences remain common:

-- Traditional sequence approach
CREATE SEQUENCE employee_seq START WITH 1 INCREMENT BY 1;

CREATE TABLE employees (
    employee_id NUMBER DEFAULT employee_seq.NEXTVAL PRIMARY KEY,
    employee_name VARCHAR2(100) NOT NULL
);

-- Modern identity column
CREATE TABLE departments (
    dept_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    dept_name VARCHAR2(50) NOT NULL
);

-- With configuration
CREATE TABLE projects (
    project_id NUMBER GENERATED BY DEFAULT AS IDENTITY 
        (START WITH 100 INCREMENT BY 10) PRIMARY KEY,
    project_name VARCHAR2(100)
);

Handling Common Production Scenarios

Synchronizing After Bulk Import

When importing data with explicit IDs, reset the sequence to prevent conflicts:

-- PostgreSQL
SELECT setval('products_product_id_seq', 
    (SELECT MAX(product_id) FROM products));

-- SQL Server
DBCC CHECKIDENT ('products', RESEED);

-- MySQL
ALTER TABLE products AUTO_INCREMENT = 1;
SELECT @max := MAX(product_id) + 1 FROM products;
SET @sql := CONCAT('ALTER TABLE products AUTO_INCREMENT = ', @max);
PREPARE stmt FROM @sql;
EXECUTE stmt;

Managing Sequence Gaps

Auto-increment values may have gaps due to failed transactions or deletions. This is normal and expected:

-- PostgreSQL: Gaps are permanent
BEGIN;
INSERT INTO orders (customer_id) VALUES (1);
-- order_id = 100
ROLLBACK;
-- Sequence advanced but row not committed

INSERT INTO orders (customer_id) VALUES (2);
-- order_id = 101 (100 is skipped)

Multi-Table Identity Strategy

For distributed systems or data partitioning, configure non-overlapping ranges:

-- Server 1: odd numbers
CREATE TABLE events_server1 (
    event_id INTEGER GENERATED BY DEFAULT AS IDENTITY 
        (START WITH 1 INCREMENT BY 2) PRIMARY KEY
);

-- Server 2: even numbers
CREATE TABLE events_server2 (
    event_id INTEGER GENERATED BY DEFAULT AS IDENTITY 
        (START WITH 2 INCREMENT BY 2) PRIMARY KEY
);

Understanding these database-specific implementations enables proper schema design, data migration strategies, and troubleshooting of identity-related issues in production environments.