Primary Keys, Foreign Keys, and Constraints
The identity guarantee a primary key provides, the referential-integrity guarantee a foreign key enforces, and the natural-vs-surrogate key decision every table forces you to make.
What you'll learn
- Explain what a primary key guarantees, and why every table needs exactly one
- Explain what a foreign key enforces, and what happens when that enforcement is violated
- Choose between a natural key and a surrogate key for a given entity, with reasoning
Prerequisites
Explanation
A primary key is the column (or set of columns) that uniquely identifies every row in a table — no two rows may share a primary key value, and it may never be NULL, both enforced by the database itself, not by application code's discipline. Every table should have exactly one; without it, there's no reliable way to reference "this specific row" from anywhere else, including from a foreign key on another table.
A foreign key is a column on one table that must match an existing primary key value on another table (or be NULL, if the relationship is optional) — this is referential integrity: the database itself refuses to let Enrollment.learner_id reference a Learner row that doesn't exist, and by default refuses to delete a Learner row that's still referenced by an Enrollment, unless the foreign key is explicitly defined with an ON DELETE behavior (CASCADE deletes the dependent rows too; SET NULL clears the reference; RESTRICT, the safer default, blocks the deletion entirely) telling the database what should happen instead. This is a genuinely different, stronger guarantee than "the application always remembers to check" — a bug in application code can forget a check; a foreign key constraint physically cannot be bypassed by a normal INSERT or DELETE.
The natural vs. surrogate key decision comes up for nearly every entity: a natural key is an attribute that's already meaningful in the real world and happens to be unique (an email address, a national ID number); a surrogate key is an artificial identifier with no meaning outside the database, most commonly an auto-incrementing integer or a generated UUID. Natural keys have a real, recurring problem: values that seem permanently unique in the real world sometimes turn out not to be (an email address can be reused after an account is deleted; two organizations can independently issue the "same" natural-looking code), and a primary key value is expensive to change once other tables reference it via foreign keys. Surrogate keys avoid this entirely by never being meaningful outside the database, which is why they're the default, standard choice in most schema designs — reserving natural keys for genuinely permanent, verified-unique identifiers (like a properly-validated national tax ID in a system built specifically around it), and adding a separate UNIQUE constraint (not the primary key itself) on a natural-key-like column such as email when uniqueness still needs enforcing.
Example
Referential integrity modeled as an explicit check -- what a real foreign key constraint enforces automatically, every time, without relying on application code remembering to check.
function insertEnrollment(learners, courses, enrollments, newEnrollment) {
const learnerExists = learners.some(l => l.id === newEnrollment.learnerId);
const courseExists = courses.some(c => c.id === newEnrollment.courseId);
if (!learnerExists) {
throw new Error("foreign key violation: learner_id " + newEnrollment.learnerId + " does not exist");
}
if (!courseExists) {
throw new Error("foreign key violation: course_id " + newEnrollment.courseId + " does not exist");
}
enrollments.push(newEnrollment);
return enrollments;
}
const learners = [{ id: 1, name: "Alice" }];
const courses = [{ id: 10, title: "PostgreSQL" }];
insertEnrollment(learners, courses, [], { learnerId: 1, courseId: 10 }); // succeeds
insertEnrollment(learners, courses, [], { learnerId: 999, courseId: 10 }); // throws -- learner 999 doesn't exist
// A REAL foreign key constraint enforces exactly this check, automatically, on every insert -- with no
// application code able to forget or bypass it.Try it yourself
Try inserting an enrollment with a valid learnerId but an invalid courseId, and observe which check fails.
Code editor. Press Escape then Tab to leave the editor if keyboard focus becomes trapped. Press Control+Shift+M inside the editor to toggle Tab-key focus trapping.
Guided exercise
Guided exercise
Write isValidPrimaryKeyCandidate(values) modeling what a primary key requires: return true only if every value in the array is non-null/non-undefined AND all values are unique (no duplicates).
Checks: accepts unique, non-null values · rejects a duplicate value · rejects a null value · an empty array is trivially valid
Code editor. Press Escape then Tab to leave the editor if keyboard focus becomes trapped. Press Control+Shift+M inside the editor to toggle Tab-key focus trapping.
Stuck? Get a hint.
Independent exercise
Independent exercise
Write deleteLearner(learners, enrollments, learnerId, onDeleteBehavior) modeling ON DELETE CASCADE / SET NULL / RESTRICT for a foreign key. 'CASCADE' removes the learner AND all their enrollments. 'SET NULL' removes the learner and sets learnerId to null on their enrollments (keeping the enrollment rows). 'RESTRICT' throws an Error and changes nothing if any enrollment still references the learner.
Checks: CASCADE removes both the learner and their enrollments · SET NULL keeps enrollments but nulls the foreign key · RESTRICT throws when a referencing row exists · RESTRICT succeeds when nothing references the row being deleted
Code editor. Press Escape then Tab to leave the editor if keyboard focus becomes trapped. Press Control+Shift+M inside the editor to toggle Tab-key focus trapping.
Stuck? Get a hint.
Common mistakes
- Relying on application code to check referential integrity ('always look up the learner before inserting an enrollment') instead of a real foreign key constraint -- a single missed check anywhere in the codebase creates an orphaned or invalid reference; a constraint makes it structurally impossible.
- Using a naturally-meaningful value like email as a PRIMARY KEY directly, then discovering it needs to change later (a corrected typo, a reused address after account deletion) -- changing a primary key that other tables reference via foreign key is expensive; a surrogate key avoids this by never needing to change for real-world reasons.
- Forgetting to specify an ON DELETE behavior on a foreign key and being surprised when PostgreSQL's default (effectively RESTRICT) blocks a deletion -- this default is a safety feature, not a bug, but it must be a deliberate decision, not a surprise.
Knowledge check
Takeaway
A primary key's uniqueness and non-null guarantees, and a foreign key's referential-integrity guarantee, are enforced by the database itself on every write — a genuinely stronger guarantee than any application-code discipline can provide; prefer a surrogate key unless a natural key is provably, permanently unique.
Summary
A primary key uniquely identifies every row and can never be null. A foreign key must match an existing primary key value (or be null, if optional), with ON DELETE CASCADE/SET NULL/RESTRICT defining what happens when the referenced row is deleted. Surrogate keys avoid the risk that a natural key's real-world uniqueness assumption turns out to be wrong.
References
Your notes
Notes save automatically.
Finished this lesson?
Mark it complete to track your progress and schedule a future review.
AI tutor
The optional AI tutor isn't enabled in this deployment. All lessons, exercises, quizzes, and search work fully without it.