How to Use Auto Increment in SQL

Use auto-incrementing IDs when every new row needs a unique numeric identifier generated automatically. This is the standard pattern for primary keys in users, orders, products, and event logs.

What you’ll build or solve

You’ll learn how to use auto increment in SQL across common database engines. You’ll also know when sequence-generated IDs are better than business-generated keys.

When this approach works best

This approach is the right choice when rows need stable unique IDs without manual assignment.

Common real-world scenarios include:

• User IDs
• Order IDs
• Product IDs
• Invoice rows
• Audit events

This is a bad idea when IDs must be globally unique across distributed systems. In that case, UUIDs may be better.

Prerequisites

You only need:

• Basic CREATE TABLE knowledge
• A numeric primary key column

Step-by-step instructions

Step 1: Use the database-specific auto-increment syntax

The syntax depends on the SQL database.

MySQL

CREATE TABLE users (
  user_id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(255)
);

PostgreSQL

CREATE TABLE users (
  user_id SERIAL PRIMARY KEY,
  email TEXT
);

SQL Server

CREATE TABLE users (
  user_id INT IDENTITY(1,1) PRIMARY KEY,
  email VARCHAR(255)
);

Each new insert automatically gets the next numeric ID.

What to look for:

• IDs are generated automatically
• Great for surrogate primary keys
• Database syntax varies
• Works best with primary keys
• Avoid using business data as identifiers

Examples you can copy

Orders table

order_id INT AUTO_INCREMENT PRIMARY KEY

Product IDs

product_id SERIAL PRIMARY KEY

Event log IDs

event_id INT IDENTITY(1,1) PRIMARY KEY

Common mistakes and how to fix them

Mistake 1: Inserting manual IDs unnecessarily

What the reader might do:

INSERT INTO users (user_id, email)
VALUES (1, 'alex@example.com');

Why it breaks: this can collide with the auto-generated sequence.

Corrected approach:

Let the database assign the ID.

Mistake 2: Using auto increment for distributed global IDs

What the reader might do:

Use integer sequences across multiple write regions.

Why it breaks: distributed systems may need globally unique identifiers.

Corrected approach:

Use UUIDs where global uniqueness matters.

Mistake 3: Expecting gapless IDs

What the reader might do:

Assume IDs will always be consecutive.

Why it breaks: rollbacks and deletes can create gaps.

Corrected approach:

Treat IDs as identifiers, not row counts.

Troubleshooting

If inserts fail, stop manually assigning duplicate IDs.

If the syntax errors, verify the database-specific keyword.

If gaps appear, this is normal behavior.

If IDs must be globally unique, switch to UUIDs.

Quick recap

• Use auto increment for generated numeric IDs
• Syntax differs by database
• Best for surrogate primary keys
• Do not rely on gapless sequences
• Use UUIDs for distributed systems