AUTO_INCREMENT vs SERIAL vs IDENTITY in SQL

Introduction

These are ways to automatically create unique numbers for new rows in a table. They help give each row a special ID without you typing it.

When you want to give each new user a unique ID automatically.

When adding new products to a list and need a unique product number.

When tracking orders and each order needs a unique number.

When you want the database to handle numbering so you don't make mistakes.

Syntax

SQL

CREATE TABLE table_name (
  id INT AUTO_INCREMENT PRIMARY KEY
);

-- OR --

CREATE TABLE table_name (
  id SERIAL PRIMARY KEY
);

-- OR --

CREATE TABLE table_name (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY
);

AUTO_INCREMENT is mainly used in MySQL.

SERIAL is a shortcut in PostgreSQL for auto-incrementing integers.

IDENTITY is the standard SQL way, used in SQL Server and newer PostgreSQL versions.

Examples

MySQL example: user_id will increase by 1 automatically for each new user.

SQL

CREATE TABLE users (
  user_id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50)
);

PostgreSQL example: product_id auto-increments using SERIAL.

SQL

CREATE TABLE products (
  product_id SERIAL PRIMARY KEY,
  product_name TEXT
);

SQL Server or modern PostgreSQL example: order_id auto-increments using IDENTITY.

SQL

CREATE TABLE orders (
  order_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_date DATE
);

Sample Program

This example creates a table with an AUTO_INCREMENT id in MySQL, adds two employees, and shows their IDs.

SQL

CREATE TABLE employees (
  emp_id INT AUTO_INCREMENT PRIMARY KEY,
  emp_name VARCHAR(100)
);

INSERT INTO employees (emp_name) VALUES ('Alice');
INSERT INTO employees (emp_name) VALUES ('Bob');

SELECT * FROM employees;

Important Notes

Each method automatically creates a unique number for new rows.

AUTO_INCREMENT is MySQL-specific; SERIAL is PostgreSQL-specific but considered legacy; IDENTITY is the modern standard.

IDENTITY allows more control over how numbers are generated compared to SERIAL.

Summary

AUTO_INCREMENT is used in MySQL for automatic numbering.

SERIAL is a PostgreSQL shortcut for auto-incrementing integers.

IDENTITY is the SQL standard and used in SQL Server and newer PostgreSQL versions.