Primary key vs unique key vs foreign key — and how do they relate to indexes?
⚡ 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.
☕Coffee Chat Question
Concept Made Simple
“Primary key vs unique key vs foreign key — and how do they relate to indexes?”
🧠Mind Map Answer
Remember It Faster
🔥What If?
Think Beyond the Expected
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:
⚠️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
- 1Why index foreign-key columns explicitly?
- 2Can a unique key contain NULLs? How many?
- 3What does ON DELETE CASCADE cost?
🧩Related Technologies
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.
Was this answer helpful?
⭐ Featured Products
Support our platform by exploring our recommended products.
As an Amazon affiliate, purchases through these links may earn us a small commission — at no extra cost to you. It helps keep Full Stack Interview Guru free.