Beginner-Friendly Topic
Take your time - it's perfectly normal to re-read this topic 2-3 times. Try the interactive code editor below to run code yourself. Use the Q&A section to check your understanding before moving on. You've got this! 🚀
Auto Increment / SERIAL
Auto-increment generates unique sequential integers automatically for primary keys - no need to specify them in INSERT. Each database has its own syntax: SERIAL/SEQUENCE in PostgreSQL, AUTO_INCREMENT in MySQL, AUTOINCREMENT in SQLite.
Auto-Increment in Different Databases
- PostgreSQL SERIAL - shorthand for INT + SEQUENCE, auto-generates next value
- PostgreSQL BIGSERIAL - for large tables (BIGINT + SEQUENCE)
- PostgreSQL IDENTITY (modern) - GENERATED ALWAYS AS IDENTITY
- MySQL AUTO_INCREMENT - set on INT/BIGINT column
- SQLite INTEGER PRIMARY KEY - auto-increment by convention
- UUID alternative - non-sequential, globally unique, better for distributed systems
Auto-Increment Examples
-- PostgreSQL SERIAL (classic)
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name VARCHAR(100)
);
INSERT INTO users (name) VALUES ('Alice'); -- id = 1 auto-assigned
INSERT INTO users (name) VALUES ('Bob'); -- id = 2 auto-assigned
-- PostgreSQL IDENTITY (modern, SQL standard)
CREATE TABLE products (
id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name VARCHAR(100)
);
-- MySQL AUTO_INCREMENT
CREATE TABLE orders (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT,
created_at DATETIME DEFAULT NOW()
);
-- Get last inserted ID
-- PostgreSQL:
INSERT INTO users (name) VALUES ('Charlie') RETURNING id;
-- MySQL:
-- SELECT LAST_INSERT_ID();
-- Reset sequence (PostgreSQL)
ALTER SEQUENCE users_id_seq RESTART WITH 1;
-- UUID primary key (distributed, globally unique)
CREATE EXTENSION IF NOT EXISTS "pgcrypto";
CREATE TABLE sessions (
id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
user_id INT REFERENCES users(id),
created_at TIMESTAMP DEFAULT NOW()
);Quick Quiz
Tip
Tip
Practice Auto Increment SERIAL in small, isolated examples before integrating into larger projects. Breaking concepts into small experiments builds genuine understanding faster than reading alone.
Common Mistake
Warning
A common mistake with Auto Increment SERIAL is skipping edge case testing - empty inputs, null values, and unexpected data types. Always validate boundary conditions to write robust, production-ready sql code.
Practice Task
Note
Practice Task - (1) Write a working example of Auto Increment SERIAL from scratch without looking at notes. (2) Modify it to handle an edge case (empty input, null value, or error state). (3) Share your solution in the Priygop community for feedback.
Key Takeaways
- Auto-increment generates unique sequential integers automatically for primary keys - no need to specify them in INSERT.
- PostgreSQL SERIAL - shorthand for INT + SEQUENCE, auto-generates next value
- PostgreSQL BIGSERIAL - for large tables (BIGINT + SEQUENCE)
- PostgreSQL IDENTITY (modern) - GENERATED ALWAYS AS IDENTITY