SQL – Auto Increment
Automatically generate unique sequential IDs for new rows using AUTOINCREMENT.
Table of Contents
AUTOINCREMENT (or AUTO_INCREMENT in MySQL, SERIAL in PostgreSQL) automatically assigns the next available integer to a column whenever a new row is inserted. This eliminates the need to manually track and supply primary key values — the database handles it for you.
The guarantee: once an ID is used, it is never reused — even if the row is deleted. This is important for audit trails and references stored outside the database (logs, URLs).
Syntax by database
-- SQLite
id INTEGER PRIMARY KEY AUTOINCREMENT
-- MySQL
id INT AUTO_INCREMENT PRIMARY KEY
-- PostgreSQL
id SERIAL PRIMARY KEY
-- or: id INTEGER GENERATED ALWAYS AS IDENTITY
Try It Yourself — Interactive SQL Editor
Edit the query below and click Run Query ▶ to see live results powered by SQLite running directly in your browser.
Key Points
- AUTOINCREMENT generates the next unique ID automatically
- IDs are never reused even after row deletion (in SQLite)
- Manual ID values can still be inserted if specified explicitly
- Do not use AUTOINCREMENT as a meaningful business identifier — use it as a technical key only
Pro Tip from CodesCompiler: The best way to learn SQL is to break things intentionally — modify the query above, change the WHERE conditions, try different columns. Every error teaches you something the docs cannot.
In the next lesson, we continue exploring SQL’s powerful feature set to build your database mastery.