Foreign Keys

Introduction

A foreign key is a column that references the primary key of another table — the way relational databases enforce relationships. Without FKs, your data integrity lives entirely in app code (and breaks).

Understanding the topic

Core concepts to understand:

• FOREIGN KEY (user_id) REFERENCES users(id).
• DB rejects inserts/updates that point to nonexistent rows.
• ON DELETE CASCADE deletes children automatically.
• ON DELETE RESTRICT/NO ACTION blocks delete if children exist.
• ON DELETE SET NULL nulls the FK.

Syntax reference

Visual workflow / architecture:

orders.user_id  ──FK──▶  users.id

Insert order with user_id=999 (no such user)
              │
              ▼
   ❌ FK violation — INSERT rejected

DELETE user 5
   └ ON DELETE CASCADE → all orders of user 5 deleted
   └ ON DELETE RESTRICT → blocked
   └ ON DELETE SET NULL → orders.user_id = NULL

Real-world use

Banks, ledgers, and any system tracking money use foreign keys + transactions to guarantee that a payment can never reference a missing account.

Best practices

• Always declare FKs — let the DB enforce integrity.
• Index every FK column — joins and CASCADE deletes need it.
• Pick CASCADE/RESTRICT consciously per relationship.

Common mistakes

• Skipping FKs 'for performance' → silent corruption later.
• Forgetting to index the FK column → slow joins.

Hands-on exercise

Interview preparation — practice these questions:

• Q1. What is a foreign key?
• Q2. ON DELETE CASCADE vs RESTRICT.
• Q3. Should you index a FK column? Why?
• Q4. Can a FK reference a UNIQUE column instead of PK?
• Q5. Cost of FK enforcement.