Welcome to the next lesson in our database design course. In the last two lessons, we defined what primary keys are, covering both simple and composite keys. We saw that the main purpose of a primary key is to provide a unique identifier for each record in a table.
Today, we'll address a common and important question: what if you find more than one potential unique identifier for an entity? This lesson focuses on how to evaluate these options (called candidate keys) and select the most appropriate primary key. We will introduce a hierarchy of key types to clarify their relationships and then discuss the practical criteria you'll use to make this crucial design decision.
1. The Hierarchy of Keys: Superkeys, Candidate Keys, and Primary Keys
To properly evaluate your options, it's helpful to understand the formal terminology used to classify keys. There's a clear hierarchy: Superkey -> Candidate Key -> Primary Key.
Let's watch a video that explains these concepts in a structured way.
This video from Neso Academy breaks down the definitions and relationships between different key types.
Watch the following four segments in order: First, understand what a Superkey is in the section from Super Key definition. A superkey is any attribute or set of attributes that can uniquely identify a row. Notice that it can contain extra, unnecessary attributes. Next, see how a Candidate Key is derived from a superkey in the section from Candidate Key definition. A candidate key is a minimal superkey—it's been reduced to the smallest number of attributes needed to ensure uniqueness. Then, learn how the Primary Key is selected from the section on Primary Keys. The primary key is the one candidate key that the database designer chooses. Pay attention to the two strict rules: it must be unique and cannot contain NULL values. Finally, understand what happens to the other candidates in the segment on Alternate Keys. These are the candidate keys that were not chosen to be the primary key.
This hierarchy can be summarized visually. A primary key is a special type of candidate key, and a candidate key is a special type of superkey.
2. Criteria for Selecting the Best Primary Key
Now that we know we select a primary key from a pool of candidate keys, how do we make the choice? A good primary key isn't just unique; it should also be stable, simple, and reliable.
Let's explore the practical criteria for evaluating candidate keys.
Database Keys Made Easy - Primary, Foreign, Candidate, Surrogate, & Many More
This video from Decomplexify provides an excellent, practical example of evaluating candidate keys for a US States table.
Watch the section from the US States example. As you watch, focus on the reasoning used to compare the candidate keys (state code and state name) and why another attribute (state capital) is rejected as a candidate.
From the video, we can distill the key criteria for selecting a primary key from the candidates:
- Uniqueness: This is the baseline requirement for any candidate key. The chosen attribute(s) must uniquely identify every record.
- Stability: The value of the primary key should ideally never change. In the video's example,
state namecould theoretically change, butstate codeis designed to be highly stable. An employee's name might change (e.g., marriage), but their assignedEmployeeIDshould not. - Simplicity: When possible, prefer a simple key over a composite one. Also, a single, short identifier (like a 2-letter state code or a numeric ID) is generally more efficient and easier to work with than a long string (like the full state name).
- Non-Null: The primary key cannot have
NULLvalues. Any candidate key that might containNULLs for some records should be disqualified.
Let's look at another example that ties all these terms together.
In the Employee table shown:
- Candidate Keys:
{EmpID},{EmpLicence},{EmpPassport}. Each of these can uniquely identify an employee. - Primary Key Selection: The designer chose
{EmpID}. This is a good choice because it's numeric (simple), and its value is controlled by the organization, ensuring it is stable and never null. - Alternate Keys:
{EmpLicence}and{EmpPassport}are the remaining candidate keys. They are unique, but might be less ideal as a primary key. For example, a passport can expire and be reissued with a new number (less stable), or an employee might not have a license (could be null).
3. Practice Exercise
Now, it's your turn to apply these evaluation criteria. This exercise requires you to think not just about what is unique, but what could be unique under different real-world conditions.
Consider the following relation for classes taught at a university:
CLASS(Course_Number, Univ_Section_Number, Instructor_Name, Semester, Building_Code, Room_Number, Time, Weekdays, Credit_Hours)
Your goal is to be able to identify each specific class offering (e.g., the 9 AM section of 'CS101' in Fall 2023 is different from the 11 AM section).
Task:
Identify three possible candidate keys for the CLASS relation. For each candidate key you propose, describe the real-world conditions under which it would be a valid unique identifier. Each candidate key should have between one and three attributes.
Solution (Click to expand)
Here are three possible candidate keys and the logic behind them.
-
Candidate Key:
{Course_Number, Semester, Univ_Section_Number}- Conditions for Validity: This key assumes that for any given course in a specific semester, the section number is unique. For example, in "Fall 2023", there can only be one "Section 1" of course "CSCI3410". This is a very common and reliable way to identify class offerings in most universities.
-
Candidate Key:
{Semester, Building_Code, Room_Number, Time, Weekdays}- Conditions for Validity: This key assumes that a specific room in a specific building cannot be booked by more than one class at the exact same time slot on the same day(s) within a given semester. For example, room "E127" in building "ECC" cannot host two different classes at 9 AM on Mondays/Wednesdays during the "Spring 2024" semester. This is also a strong candidate, reflecting physical constraints.
-
Candidate Key:
{Semester, Instructor_Name, Time, Weekdays}- Conditions for Validity: This key relies on the assumption that an instructor cannot teach two different classes at the exact same time. For instance, "John Smith" cannot teach two different courses at 2 PM on Tuesdays/Thursdays during the "Fall 2023" semester. While generally true, this might be a slightly weaker candidate than the others. What if two instructors have the exact same name? Or what if a course is co-taught? However, in many scenarios, this would still work as a unique identifier.
Which would you choose as the Primary Key?
Of these three, {Course_Number, Semester, Univ_Section_Number} is likely the best choice for a primary key. It is based on administrative identifiers (Course_Number, Univ_Section_Number) that are explicitly designed to be unique and stable for this purpose. The other keys rely more on logistical constraints which, while logical, might have edge cases.
Conclusion
In this lesson, you learned how to methodically approach the selection of a primary key. This is a fundamental skill in database design that ensures your data model is robust and reliable.
Here are the key takeaways:
- Superkey: Any set of attributes that is unique.
- Candidate Key: A minimal superkey (no redundant attributes). These are your potential choices for the primary key.
- Primary Key: The one candidate key selected by the designer to be the official identifier.
- Alternate Keys: The candidate keys that were not selected.
- Evaluation Criteria: The best primary keys are not only unique but also stable, simple, and guaranteed to be non-null.
So far, we have covered how to identify entities, attributes, and primary keys. In the next lesson, we will complete our understanding of the fundamental building blocks of relationships by defining and identifying foreign keys. These are the attributes that create the actual links between your tables.