What's the difference between a primary key, a candidate key, and a foreign key?

CREATE TABLE users (
    id       SERIAL PRIMARY KEY,          -- chosen primary key
    email    VARCHAR(255) UNIQUE NOT NULL, -- also a candidate key, just not chosen as primary
    username VARCHAR(50)  UNIQUE NOT NULL  -- another candidate key
);

CREATE TABLE orders (
    id      SERIAL PRIMARY KEY,
    user_id INT NOT NULL REFERENCES users(id)  -- foreign key
);

Candidate key

Any column (or minimal combination of columns) that could uniquely identify a row. users above has three candidate keys: id, email, and username — each alone is sufficient to find exactly one row, and none of them can be shrunk further and remain unique (that minimality requirement is what separates a candidate key from just "any unique combination").

Primary key

The one candidate key selected as the table's canonical identifier. Practical differences from other candidate keys:

• Implicitly NOT NULL (a candidate key enforced only with UNIQUE can still allow NULL, depending on engine).
• Used by default as the target of foreign keys from other tables.
• Often backs the table's clustering/physical storage order (see clustered index question).
• A table can have only one primary key, but multiple other UNIQUE constraints (candidate keys) alongside it.

Foreign key

A column in the referencing table whose values must match an existing value in the referenced table's primary/unique key (or be NULL, if the column is nullable). It's the mechanism that enforces relationships between tables:

INSERT INTO orders (user_id) VALUES (999);
-- ERROR: violates foreign key constraint — no user with id 999 exists

Foreign keys also govern cascade behavior (ON DELETE CASCADE, ON DELETE SET NULL, ON DELETE RESTRICT) — see the referential integrity question for details.

Why the distinction matters in interviews

Interviewers use this question to check that you understand uniqueness (candidate key) is a broader concept than the chosen identifier (primary key), and that a foreign key isn't a special data type — it's a constraint enforcing that a value in one table must correspond to a real row in another.