Keys & Integrity Constraints

The last lesson kept leaning on keys without ever defining them — time to pay that debt. How does a table guarantee “this row, not that one”? With a key — a small set of columns that no two rows ever share. And how do tables link to one another without lying? With a foreign key — a column that promises “whatever value I hold really exists over there.” Keys are the spine of every relational schema; once you can sort super → candidate → primary → foreign in your head, the integrity rules drop out for free. This is also why a botched key choice is the root cause behind half the “duplicate rows” and “orphaned record” bugs you will ever debug in a production data pipeline.

The four key types

Imagine a Students(id, email, name, age) table where both id and email are unique per student (no two students share either).

• Superkey — any attribute set whose values are unique across all rows. {id}, {email}, {id, name}, {id, email, age} are all superkeys. So is the whole row. There are typically many.
• Candidate key — a minimal superkey. Remove any one attribute and it stops being unique. {id} is minimal — drop id and you have nothing. {email} is minimal too. But {id, name} is not a candidate: dropping name still gives uniqueness, so it was not minimal.
• Primary key — one chosen candidate key, designated by the designer. Conventionally {id}. The other candidate keys become alternate keys.
• Foreign key — an attribute (or set) in one table that references the primary key of another. Enrolls.student_id references Students.id.

Two integrity rules enforce all this:

• Entity integrity — the primary key cannot be NULL (and is unique by construction). Without a value you cannot identify the row.
• Referential integrity — a foreign key value must either match an existing primary key in the referenced table, or be NULL. (NULL just means “this row is not linked yet.”)

How GATE asks this

Two patterns. First, MCQ on key types — given a relation and a set of functional dependencies (rules like A → B meaning “knowing A fixes B”), ask which subsets are candidate keys, or how many candidate keys exist. Second, MSQ on integrity rules — pick which of four statements about NULL, PK, and FK are correct. The questions reward precision: “superkey” vs “candidate key” hinges entirely on the word minimal.

Worked example

Take Students(id, email, name, age) where both id and email are unique per student.

• Superkeys — {id}, {email}, {id, email}, {id, name}, {email, age}, …, all the way up to the whole row. Many.
• Candidate keys — minimal superkeys. {id} is minimal (drop it and the remaining empty set is not unique). {email} is minimal for the same reason. {id, name} is not a candidate — dropping name still gives the unique {id}, so it was not minimal. So there are exactly two candidate keys: {id} and {email}.
• Primary key — pick one. Convention says {id}. Then {email} becomes an alternate key.
• Foreign key — if Enrolls(student_id, course_id, grade) exists, student_id is a foreign key referencing Students.id. Inserting a row with student_id = 999 when no student has id = 999 violates referential integrity.

A question to carry forward

You can now design tables, choose their keys, and guarantee — via entity and referential integrity — that the data inside never lies. But a database you can only fill and never question is a filing cabinet, not a tool. The whole point of storing rows is to be able to ask for exactly the ones you want: “the names of every student older than 20,” “which courses Asha is enrolled in.” Here is the thread onward: is there a precise, composable language for pulling chosen rows and columns out of a relation — one built from a handful of operators you can chain like arithmetic — that every SQL query is secretly translated into?

In one breath

• Superkey = any attribute set unique across rows. Candidate key = a minimal superkey (drop anything and uniqueness breaks). Primary key = one chosen candidate key (others become alternate keys). Foreign key = references another table’s PK.
• The whole hierarchy hinges on minimal: every candidate key is a superkey, but not vice versa.
• Entity integrity: a primary key cannot be NULL. Referential integrity: a foreign key must match an existing PK or be NULL.
• A NULL foreign key is legal (“not linked yet”); a foreign key pointing at a non-existent PK is the violation.
• Find candidate keys from FDs: an attribute set is a key if its closure is every attribute and no proper subset already is.

Practice