SQL AUTO INCREMENT field

SQL Tutorial

SQL AUTO INCREMENT field

Here is a clear, practical, and complete guide to the SQL AUTO INCREMENT field, including syntax for MySQL, PostgreSQL, SQL Server, and Oracle.

✅ What Is an AUTO INCREMENT Field?

An AUTO INCREMENT (or identity) field automatically generates sequential numeric values whenever a new row is inserted.

Used commonly for:

  • Primary keys

  • Surrogate IDs

  • Unique identifiers

Each database engine implements it differently.

🟦 MySQL — AUTO_INCREMENT

CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100)
);

Insert without specifying id:

INSERT INTO users (name) VALUES ('Alice');

MySQL automatically generates:

  • 1, 2, 3, …

Set starting value:

ALTER TABLE users AUTO_INCREMENT = 1000;

🟪 PostgreSQL — SERIAL / BIGSERIAL / GENERATED

Legacy (Common):

CREATE TABLE users (
id SERIAL PRIMARY KEY,
name TEXT
);

Types:

  • SERIAL → INT

  • BIGSERIAL → BIGINT

Modern Standard (Recommended):

CREATE TABLE users (
id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name TEXT
);

Equivalent to SQL standard.

🟧 SQL Server — IDENTITY

CREATE TABLE users (
id INT IDENTITY(1,1) PRIMARY KEY,
name VARCHAR(100)
);

Format:

IDENTITY(seed, increment)

Example:

id INT IDENTITY(100, 10)
-- Generates: 100, 110, 120, …

🟨 Oracle — Identity Column (12c+)

CREATE TABLE users (
id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
name VARCHAR2(100)
);

Older versions used SEQUENCES + TRIGGERS.

🔥 Insert Behavior

AUTO INCREMENT fields fill automatically:

INSERT INTO users (name)
VALUES ('Bob');

The DB generates the next ID.

🔥 Retrieve Last Inserted ID

MySQL:

SELECT LAST_INSERT_ID();

PostgreSQL:

INSERT INTO users (name)
VALUES ('Bob')
RETURNING id;

SQL Server:

SELECT SCOPE_IDENTITY();

Oracle:

RETURNING id INTO :variable;

📌 Reset AUTO INCREMENT

MySQL:

ALTER TABLE users AUTO_INCREMENT = 1;

PostgreSQL:

ALTER SEQUENCE users_id_seq RESTART WITH 1;

SQL Server:

DBCC CHECKIDENT ('users', RESEED, 0);

🧠 Important Behavior Notes

✔ AUTO INCREMENT fields must be numeric
✔ They are often PRIMARY KEY, but not required
✔ Gaps in sequences are normal (due to deletes or rolled-back transactions)
✔ AUTO INCREMENT is not guaranteed gap-free
✔ Not suitable for sensitive identifiers (use UUID instead)

🧩 REAL-WORLD EXAMPLE

User Table with Auto IDs (MySQL)

CREATE TABLE users (
user_id INT AUTO_INCREMENT PRIMARY KEY,
email VARCHAR(255) UNIQUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Orders Table with Identity (SQL Server)

CREATE TABLE orders (
order_id INT IDENTITY(1,1) PRIMARY KEY,
amount DECIMAL(10,2) NOT NULL
);

Products Table with SERIAL (PostgreSQL)

CREATE TABLE products (
id BIGSERIAL PRIMARY KEY,
name TEXT NOT NULL,
price NUMERIC(10,2)
);

🏎 Best Practices

✔ Prefer system-managed surrogate keys (AUTO INCREMENT, SERIAL, IDENTITY)
✔ Use natural keys only when truly stable
✔ Don’t rely on sequence having no gaps
✔ For distributed systems → use UUID instead
✔ Always make AUTO INCREMENT the PRIMARY KEY

You may also like...