Study smarter with Fiveable
Get study guides, practice questions, and cheatsheets for all your subjects. Join 500,000+ students with a 96% pass rate.
Database keys are the backbone of relational database design—they're how you ensure data stays accurate, relationships stay intact, and queries return exactly what you need. On exams, you're being tested on more than just definitions: you need to understand why a designer would choose one key type over another, how keys enforce referential integrity, and what happens when key constraints are violated. These concepts connect directly to normalization, entity-relationship modeling, and query optimization.
The key types fall into distinct categories based on their purpose: some exist to uniquely identify records, others to establish relationships, and still others to improve performance. Don't just memorize a list of nine key types—know what problem each one solves and when you'd reach for it in a real schema design. That conceptual understanding is what separates a passing answer from a strong one.
These keys solve the fundamental problem of distinguishing one record from another. The core principle: every row in a table must be uniquely addressable, or your data becomes unreliable.
Compare: Candidate Key vs. Alternate Key—both guarantee uniqueness, but candidate keys are potential primary keys while alternate keys are the runners-up after selection. If an exam asks which key "could have been" the primary key, the answer is any candidate key (including the one that became an alternate).
These keys are categorized by how many columns they contain and what they include. Understanding the hierarchy here—super key → candidate key → primary key—is frequently tested.
Compare: Super Key vs. Composite Key—a super key is defined by what it can do (uniquely identify), while a composite key is defined by how it's built (multiple columns). A composite key is always a super key, but a super key with extra unnecessary columns isn't minimal enough to be a candidate key.
Foreign keys are the mechanism that links tables together, enabling the relational model to work. Without foreign keys, you'd have isolated tables with no way to enforce valid references.
Compare: Primary Key vs. Foreign Key—primary keys identify records within a table; foreign keys reference records across tables. A foreign key in one table points to a primary key in another, creating the parent-child relationships that make joins possible.
This distinction matters for real-world database design. The debate between natural and surrogate keys comes down to stability versus meaning.
Compare: Natural Key vs. Surrogate Key—natural keys are meaningful but fragile; surrogate keys are meaningless but stable. FRQs often ask you to justify choosing one over the other: use natural keys when the real-world identifier is truly permanent (like country codes), surrogate keys when business data might change.
These keys focus on enforcement and access speed rather than identification or relationships.
Compare: Unique Key vs. Secondary Key—unique keys are constraints that prevent duplicates; secondary keys are access paths that improve performance. A unique key always creates an index, but a secondary key index doesn't enforce any constraint.
| Concept | Best Examples |
|---|---|
| Uniquely identifies records | Primary Key, Candidate Key, Alternate Key |
| Key hierarchy (broad to narrow) | Super Key → Candidate Key → Primary Key |
| Multi-column identification | Composite Key |
| Cross-table relationships | Foreign Key |
| Real-world vs. artificial identifiers | Natural Key, Surrogate Key |
| Constraint enforcement | Primary Key (no NULLs), Unique Key (allows NULL) |
| Performance optimization | Secondary Key, Unique Key (both create indexes) |
| Many-to-many junction tables | Composite Key (often as Primary Key), Foreign Keys |
What's the difference between a super key and a candidate key? Give an example where adding a column to a candidate key creates a super key that's no longer minimal.
You're designing a table for student course enrollments. Why would you use a composite key of {StudentID, CourseID} rather than creating a new surrogate key?
Compare natural keys and surrogate keys: under what circumstances would you choose each? What are the trade-offs for data integrity and query readability?
A table has three candidate keys: {Email}, {EmployeeID}, and {SSN}. You choose EmployeeID as the primary key. What are the other two keys now called, and can they still be used to uniquely access records?
Explain why a foreign key can contain NULL values while a primary key cannot. Describe a real-world scenario where a nullable foreign key makes sense.