EasyExperience level: 0-2 years · 3-5 years1 min read

Primary key vs unique key vs foreign key — and how do they relate to indexes?

Reviewed by Gurusankar M.

Asked inTCSInfosysDeloitteWipro
#primary key#unique key#foreign key#constraints#index
Report issue

Short Answer

PRIMARY KEY = unique + NOT NULL, one per table, usually the clustered index. UNIQUE = enforces uniqueness, allows (typically one) NULL, multiple per table. FOREIGN KEY = references another table's key for referential integrity. PK/UNIQUE auto-create indexes; FK columns should be indexed too.

On this page

Concept Made Simple

Coffee Chat Question

“Primary key vs unique key vs foreign key — and how do they relate to indexes?”

Remember It Faster

Mind Map Answer

PRIMARY KEY
unique + NOT NULL, one per table
UNIQUE
unique, allows NULL, many per table
FOREIGN KEY
referential integrity to a parent

Think Beyond the Expected

What If?

Deletes/joins on a child table are slow — what's a common missing index?

The foreign-key column. PK/UNIQUE auto-index, but FK columns usually don't get an index automatically — so joins and cascade/check-on-delete do full scans. Add an index on every FK column.

Real World

Un-indexed foreign keys are a classic cause of slow joins and lock escalation on parent deletes; adding the FK index is a frequent, high-impact fix.

Interviewer's Expectation

Keywords they're listening for:

  • PK unique+not null+1
  • UNIQUE allows null/many
  • FK referential integrity
  • PK/UNIQUE auto-index
  • index FK columns manually

Common Mistakes

  • Leaving FK columns un-indexed
  • Assuming UNIQUE disallows all NULLs
  • Multiple primary keys (only one allowed)

Best Practices

  • Index every foreign-key column
  • Use a stable surrogate PK (usually)
  • Enforce integrity with constraints, not app code alone

Follow-up Questions

  1. Why index foreign-key columns explicitly?
  2. Can a unique key contain NULLs? How many?
  3. What does ON DELETE CASCADE cost?

Continue Learning with AI

Take this question deeper with your favourite AI assistant. Pick a depth, copy the prompt, or open it directly — AI is your learning companion, not a shortcut.

Plain-language foundations

I'm preparing for a software engineering interview and want to understand this from scratch, as a beginner.

Topic: Normalization (SQL)
Interview question: "Primary key vs unique key vs foreign key — and how do they relate to indexes?"

Please:
1. Explain the core idea in simple, plain language, using an everyday analogy.
2. Define any technical terms you use.
3. Walk through one small, concrete example.
4. Finish with a single sentence I can easily remember.

Keep the tone friendly and assume I'm new to this topic.
Open inChatGPTGeminiClaude

Was this answer helpful?

Affiliate links

Tools for Your Developer Workspace

Equipment for a comfortable, productive setup.

As an Amazon Associate I earn from qualifying purchases.