This Is Where Students Break

Keys Deep Dive

Candidate, Surrogate, Foreign.
The three keys to database sanity.

🎯

Candidate

All possible IDs

🧬

Surrogate

The chosen one

🔗

Foreign

The promise

Candidate Keys (All Possible IDs)

Click each to test: "Will this still work 10 years later?"

STUDENT Table Candidates

student_id

System-generated, meaningless, never changes ✓

email

Changes when student graduates, switches providers, or marries

phone

Changes constantly, country codes, number portability

citizenship_no

Not all students have one, format changes, privacy issues

Only one survives. The others become regular columns with UNIQUE constraints.

Why We Invent IDs

Natural keys change. Surrogate keys don't.

📧

Natural Key

Has meaning in the real world

john.smith@email.com

❌ Changes, has meaning, not unique forever

→

🧬

Surrogate Key

Invented by the database

S1002847

✓ Immutable, meaningless, eternal

"Databases prefer meaningless numbers.
Humans prefer meaning."

Foreign Key = A Promise

Not syntax. Meaning. A relationship enforced by the database itself.

🔗

"This value must exist somewhere else"

ENROLLMENT

enrollment_id

student_id

course_id

enroll_date

PROMISE

STUDENT

student_id

name

COURSE

course_id

title

Life Without Foreign Keys

Click to reveal what happens when promises are broken.

The Ghost Enrollment

👻

enrollment_id: E5001

student_id: 999

course_id: CS101

Student 999 does not exist in the STUDENT table.

🤔

Is this data or fiction?

Without FK constraints: It's "valid" data.
With FK constraints: IMPOSSIBLE to insert.

Click the ghost enrollment to see the truth.

The Three Keys: Summary

🎯

Candidate Key

All possible identifiers. We choose one to be Primary.

🧬

Surrogate Key

The chosen Primary Key. Meaningless, immutable, system-generated.

🔗

Foreign Key

A promise that data exists elsewhere. Prevents orphans.

"Master these three keys, and you master relational thinking."