Topic 22 of 52
PRIMARY KEY
Overview
A PRIMARY KEY uniquely identifies each row in a table. Every table should have one. It automatically enforces uniqueness and NOT NULL, and the database creates an index on it for fast lookups.
Syntax
sql
-- Simple primary key
CREATE TABLE users (
id SERIAL PRIMARY KEY,
email VARCHAR(255) NOT NULL
);
-- UUID primary key (recommended for distributed systems)
CREATE TABLE orders (
id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
...
);
-- Composite primary key (multiple columns together = unique)
CREATE TABLE order_items (
order_id INT REFERENCES orders(id),
product_id INT REFERENCES products(id),
quantity INT NOT NULL,
PRIMARY KEY (order_id, product_id) -- combination is unique
);
-- Add primary key to existing table
ALTER TABLE sessions ADD PRIMARY KEY (session_token);
-- Auto-increment types by database
SERIAL / BIGSERIAL -- PostgreSQL
AUTO_INCREMENT -- MySQL
IDENTITY(1,1) -- SQL Server
AUTOINCREMENT -- SQLiteCommon Pitfalls
- Never use meaningful business data (like email, SSN) as primary keys — they can change. Use a surrogate key (SERIAL/UUID) instead.
- SERIAL in PostgreSQL is just a sequence + NOT NULL + DEFAULT — it is NOT a true autoincrement and the sequence can go out of sync.
- Interview tip: UUID vs SERIAL — UUID is safe for distributed systems and public APIs (IDs aren't guessable); SERIAL is simpler and slightly faster.
Real-World Example
Choosing the right primary key strategy for different tables:
example
sql
-- SERIAL for simple internal tables
CREATE TABLE categories (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL UNIQUE,
slug VARCHAR(100) NOT NULL UNIQUE
);
-- UUID for user-facing entities (not guessable, safe for URLs)
CREATE TABLE users (
id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
email VARCHAR(255) NOT NULL UNIQUE,
created_at TIMESTAMPTZ DEFAULT NOW()
);
-- Composite PK for junction/pivot tables
CREATE TABLE user_roles (
user_id INT REFERENCES users(id) ON DELETE CASCADE,
role_id INT REFERENCES roles(id) ON DELETE CASCADE,
assigned_at TIMESTAMPTZ DEFAULT NOW(),
PRIMARY KEY (user_id, role_id)
);
-- Natural key when a real-world attribute is always unique
CREATE TABLE currencies (
code CHAR(3) PRIMARY KEY, -- 'INR', 'USD', 'EUR'
name VARCHAR(50) NOT NULL,
symbol VARCHAR(5)
);