Primary Keys and Uniqueness
Understand primary keys, surrogate vs natural keys, composite keys, and why every row needs a unique identifier you can trust.
Primary Keys and Uniqueness is a free SQL Academy lesson on CoddyKit — lesson 2 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.
Why Every Row Needs a Unique ID
To update or delete a specific row safely, you must be able to point at it unambiguously.
- Two users named "Alice Adams" can exist
- But two users with
id = 42must NOT exist - The unique identifier is called the primary key
Declaring a Primary Key
You mark a column as the primary key when you create the table:
CREATE TABLE products (
id BIGSERIAL PRIMARY KEY,
sku VARCHAR(50) NOT NULL,
name VARCHAR(200) NOT NULL,
price NUMERIC(10,2) NOT NULL
);Primary Key = NOT NULL + UNIQUE
A PRIMARY KEY automatically enforces:
- NOT NULL — primary key values cannot be missing
- UNIQUE — no two rows can share the same primary key value
The database also creates a unique index on the primary key, so lookups by id are fast.
Surrogate vs Natural Keys
You have two choices for the primary key:
- Surrogate — a synthetic ID the database generates (BIGSERIAL, UUID). Most common in modern apps.
- Natural — a real-world identifier (email, ISBN). Risky: people change emails.
Best practice: use a surrogate ID and put a UNIQUE constraint on the natural key.
BIGSERIAL: Auto-Incrementing Integers
In PostgreSQL, BIGSERIAL creates a 64-bit auto-incrementing column backed by a sequence.
CREATE TABLE orders (
id BIGSERIAL PRIMARY KEY,
user_id BIGINT NOT NULL,
total NUMERIC(10,2) NOT NULL
);
-- You omit id on insert and the DB fills it in:
INSERT INTO orders (user_id, total) VALUES (7, 99.50);UUID Primary Keys
An alternative is a 128-bit UUID, useful when IDs must be unique across systems or generated client-side.
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE TABLE sessions (
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
user_id BIGINT NOT NULL,
ip INET
);Composite Primary Keys
Sometimes the natural identifier is a combination of columns:
CREATE TABLE order_items (
order_id BIGINT NOT NULL,
product_id BIGINT NOT NULL,
quantity INT NOT NULL,
PRIMARY KEY (order_id, product_id)
);
-- The pair (order_id, product_id) must be unique
-- A product can appear at most once per orderUNIQUE Constraints vs Primary Key
You can declare other columns as unique without making them the primary key:
CREATE TABLE users (
id BIGSERIAL PRIMARY KEY,
email VARCHAR(255) NOT NULL UNIQUE,
username VARCHAR(50) UNIQUE
);
-- INSERT below fails on the second row:
INSERT INTO users (email, username) VALUES ('a@x.com', 'alice');
INSERT INTO users (email, username) VALUES ('a@x.com', 'bob'); -- ERRORWhat Happens on a Duplicate Insert
If you try to insert a duplicate primary key, the database rejects the entire statement:
ERROR: duplicate key value violates unique constraint "users_pkey" DETAIL: Key (id)=(42) already exists.
This is good — it stops silent data corruption.
Looking Rows Up by Primary Key Is Fast
The unique index on the primary key gives you O(log n) lookups even at billion-row scale.
-- Index Scan using users_pkey on users (cost=0.43..8.45 rows=1)
SELECT email FROM users WHERE id = 42;Don't Reuse IDs
Even when you delete a row, do NOT reuse its primary key. Other tables or external systems may still reference it. Let the sequence keep ticking up — IDs are cheap.
Recap
Primary keys give every row a stable, unique handle.
- Prefer surrogate keys (BIGSERIAL or UUID)
- Use UNIQUE for natural keys that must not duplicate
- Composite primary keys model "this pair must be unique"
Quick Check
Which is implicitly enforced by a PRIMARY KEY?
Frequently asked questions
Is the “Primary Keys and Uniqueness” lesson free?
Yes — the full text of “Primary Keys and Uniqueness” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.
What will I learn in “Primary Keys and Uniqueness”?
Understand primary keys, surrogate vs natural keys, composite keys, and why every row needs a unique identifier you can trust. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.
Do I need any experience to start SQL Academy?
No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 2 of 4, so you can start here or from the beginning and move at your own pace.
How long does the “Primary Keys and Uniqueness” lesson take?
Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.
Can I write and run code in this SQL Academy lesson?
Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.