Keys in the Relational Model

Definition

A key is one attribute or a minimal set of attributes whose values uniquely identify each tuple in a relation. Different key types serve different roles:

• Super Key: any set of attributes that uniquely identifies a tuple (may contain extra, unnecessary attributes).
• Candidate Key: a MINIMAL super key — no attribute can be removed from it without losing the uniqueness guarantee.
• Primary Key: the candidate key chosen by the designer as the main identifier; it can never be NULL (entity integrity).
• Alternate Key: any candidate key that was NOT chosen as the primary key.
• Foreign Key: an attribute (or set) in one relation that references the primary key of another (possibly the same) relation, enforcing referential integrity.
• Composite Key: a key made of two or more attributes together (needed only when no single attribute is unique on its own).

Example

In Students(id, name, department, gpa):

• {id} is a candidate key (assuming ids are unique and never NULL) — and since it's the smallest such set, it's minimal.
• {id, name} is a super key (it also uniquely identifies tuples) but NOT a candidate key, because it isn't minimal — {id} alone already suffices.
• If email is also unique per student, {email} is another candidate key → {id} is chosen as Primary Key, and {email} becomes an Alternate Key.
• In Enrollments(student_id, course_id, grade), {student_id, course_id} is a Composite Key (neither attribute alone is unique, but the pair is), and student_id is also a Foreign Key referencing Students.id.

How this differs from Relational Integrity (its closest sibling)

Keys are the STRUCTURAL MECHANISM — which attributes get special uniqueness/reference status. Relational Integrity is the RULE that USES that mechanism: entity integrity requires primary keys to be non-NULL and unique; referential integrity requires foreign key values to match existing primary keys. You cannot state or enforce entity/referential integrity without first having defined keys — keys are the "nouns," integrity rules are the "verbs" that act on them.

Edge Cases

• A relation can lack a declared candidate key only in a loose, practical sense — since a relation is a set (no duplicate tuples by definition), the full set of ALL its attributes is always trivially a super key. A "keyless" table one sees in practice (e.g., raw log data with genuinely duplicate SQL rows) is technically not a pure relation.
• A composite key's individual attributes may repeat across many rows (student_id repeats across many enrollments) — only the COMBINATION must be unique.
• A foreign key MAY be NULL (e.g., an optional advisor_id), even though the primary key it references can never be NULL — these follow different integrity rules (referential vs. entity integrity).

Key Takeaways / Q&A

Q: Is every candidate key also a super key? A: Yes — every candidate key is a super key, but not every super key is a candidate key (only minimal ones qualify as candidate keys).

Q: Can a table have more than one candidate key but only one primary key? A: Yes — all unchosen candidate keys become alternate keys; exactly one candidate key is designated as the primary key.