🔢 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.