SQL AUTO INCREMENT – Generate Unique Sequential IDs Automatically

Introduction – What is SQL AUTO INCREMENT?

The AUTO INCREMENT feature in SQL allows a column to automatically generate a unique number whenever a new row is inserted. It’s commonly used for primary key columns to create IDs without manual input.

In this guide, you’ll learn:

• How to define AUTO INCREMENT across SQL dialects
• Default behavior, starting values, and reset options
• Differences between MySQL, PostgreSQL, and SQL Server

1. MySQL AUTO_INCREMENT Syntax

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

Automatically assigns a new id to each inserted row.

🐘 2. PostgreSQL – Use SERIAL or IDENTITY

-- Option 1: SERIAL
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100)
);

-- Option 2: Standard SQL IDENTITY
CREATE TABLE users (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name VARCHAR(100)
);

SERIAL is shorthand; IDENTITY is SQL-compliant.

3. SQL Server – IDENTITY Column

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

Starts from 1, increments by 1 for each new row.

4. Reset or Restart AUTO INCREMENT

-- MySQL
ALTER TABLE users AUTO_INCREMENT = 1000;

-- PostgreSQL
ALTER SEQUENCE users_id_seq RESTART WITH 1000;

-- SQL Server
DBCC CHECKIDENT ('users', RESEED, 1000);

Useful when archiving or reseeding tables.

5. AUTO INCREMENT Notes

• One AUTO INCREMENT column per table (typically PRIMARY KEY)
• Value is not reused if a row is deleted
• Cannot insert a manual value unless explicitly allowed
• Some engines allow gaps (e.g., rollback after failure)

Best Practices

Summary – Recap & Next Steps

AUTO INCREMENT is a powerful feature to automatically generate unique keys. It simplifies row insertion and ensures consistent primary key assignment.

Key Takeaways:

• Use AUTO_INCREMENT, SERIAL, or IDENTITY depending on DBMS
• Automatically assigns sequential values
• Reset carefully using ALTER/RESEED commands

Real-World Relevance:
Used in customer IDs, order numbers, ticketing systems, and logs.

FAQ – SQL AUTO INCREMENT

Can I have more than one AUTO INCREMENT column?

No. Most DBMS allow only one per table.

What happens if I insert a value manually?

Depends on DBMS settings. Some allow overrides, others raise errors.

Does AUTO INCREMENT fill gaps after deletions?

No. It continues from the last highest value.

Is AUTO INCREMENT zero-based?

No. Most systems start at 1 by default.