What's the difference between a primary key, foreign key, unique constraint, and index?
Take a moment.
Say your answer out loud or write it down first — retrieval is what makes it stick. Reveal when you're ready.
Short answer
A primary key uniquely identifies each row (one per table, never NULL). A foreign key links a column to another table's primary key to enforce referential integrity. A unique constraint forbids duplicates but isn't the row's identity. An index is a performance structure that speeds up lookups — keys usually get one automatically, but indexes alone enforce nothing.
Detailed explanation
Primary key: the table's identity — exactly one per table, values unique and NOT NULL. The database typically builds a B-tree index behind it so lookups by id are O(log n). Foreign key: a column (or columns) whose values must match an existing primary key in the referenced table — it prevents orphan rows (you can't insert an order for customer 999 if that customer doesn't exist) and can cascade actions (ON DELETE CASCADE removes child rows automatically). Unique constraint: guarantees no two rows share a value (emails, usernames) and usually creates an index too, but unlike a primary key a table can have many, and (in most databases) they may allow one NULL. Index: purely about speed — an auxiliary sorted structure mapping column values to row locations. You can index any frequently-queried column with no uniqueness meaning at all. Rule of thumb: constraints protect correctness (primary/foreign/unique), indexes buy speed; the database happens to implement most constraints with an index under the hood.
Example
CREATE TABLE users (
id SERIAL PRIMARY KEY, -- identity, indexed automatically
email TEXT UNIQUE NOT NULL, -- no duplicates, indexed automatically
team_id INTEGER REFERENCES teams(id) ON DELETE SET NULL -- must exist in teams
);
-- A non-unique performance index: correctness unchanged, reads faster
CREATE INDEX idx_users_team ON users(team_id);Interview tip
Frame it as correctness vs speed: keys and unique constraints protect data, indexes buy performance. The follow-up interviewers love is what ON DELETE CASCADE does.
Common mistakes
- Saying an index enforces uniqueness — only a unique index does.
- Not knowing a table has exactly one primary key but many unique constraints.