1️⃣ Candidate Key

Definition

A candidate key is any minimal set of columns that can uniquely identify a row in a table.

Properties

• Uniqueness
• Minimal (no redundant columns)
• There can be multiple candidate keys per table

Example

users
-----
id
email
passport_number

Candidate keys:

id

email

passport_number

Each could uniquely identify a user.

2️⃣ Primary Key

Definition

A primary key is one chosen candidate key that becomes the main row identifier.

Properties

• Exactly one per table
• NOT NULL
• UNIQUE
• Often used by foreign keys
• Logical identity of the row

Example

PRIMARY KEY (id)

Here:

• id is selected from candidate keys
• Others remain potential identifiers

🔑 Primary key = chosen candidate key

3️⃣ UNIQUE Constraint

Definition

A UNIQUE constraint enforces uniqueness of values, but does not define row identity.

Properties

• Can be multiple per table
• May allow NULLs (DB-dependent)
• Not necessarily minimal
• Not used as the main identity

Example

UNIQUE (email)

Key Differences (this is the core)

| Aspect       | Candidate Key          | Primary Key          | UNIQUE Constraint |
| ------------ | ---------------------- | -------------------- | ----------------- |
| Purpose      | Theoretical uniqueness | Logical row identity | Data integrity    |
| Count        | Many                   | One                  | Many              |
| NULL allowed | No                     | No                   | Usually yes       |
| Minimal      | Yes                    | Yes                  | No guarantee      |
| Used by FKs  | Possible               | Yes                  | Usually no        |

Important nuance (Postgres / SQL detail ⚠️)

UNIQUE vs PRIMARY KEY

• PRIMARY KEY = UNIQUE + NOT NULL
• UNIQUE may allow multiple NULLs
  • In PostgreSQL: multiple NULLs allowed
  • In SQL Server: single NULL allowed

Interviewers love this detail.

---

Real-world modeling example

users (
  id BIGSERIAL PRIMARY KEY,
  email TEXT UNIQUE NOT NULL,
  phone TEXT UNIQUE,
  passport_number TEXT UNIQUE
)

Conceptually:

• Candidate keys: id, email, passport_number
• Primary key: id
• UNIQUE constraints: enforce business rules

One-sentence interview answer (perfect)

A candidate key is any minimal set of columns that uniquely identifies a row, a primary key is the chosen candidate key used as the table’s main identity, and a UNIQUE constraint simply enforces uniqueness without defining row identity.

Common interview mistakes ❌

• Saying “unique key” instead of candidate key
• Thinking UNIQUE = PRIMARY KEY
• Forgetting NULL behavior
• Thinking there can be multiple primary keys