SQL AUTO INCREMENT field
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
Insert without specifying id:
MySQL automatically generates:
1, 2, 3, …
Set starting value:
PostgreSQL — SERIAL / BIGSERIAL / GENERATED
Legacy (Common):
Types:
SERIAL→ INTBIGSERIAL→ BIGINT
Modern Standard (Recommended):
Equivalent to SQL standard.
SQL Server — IDENTITY
Format:
Example:
Oracle — Identity Column (12c+)
Older versions used SEQUENCES + TRIGGERS.
Insert Behavior
AUTO INCREMENT fields fill automatically:
The DB generates the next ID.
Retrieve Last Inserted ID
MySQL:
PostgreSQL:
SQL Server:
Oracle:
Reset AUTO INCREMENT
MySQL:
PostgreSQL:
SQL Server:
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)
Orders Table with Identity (SQL Server)
Products Table with SERIAL (PostgreSQL)
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
