Hello! In our last lesson, we focused on identifying entities and the attributes that describe them. We concluded by asking a critical question: if we have two customers with the same name, how does the database tell them apart? This lesson provides the answer.
Today, we will explore the concept of the primary key (PK). This is a fundamental building block in relational databases. You will learn what a primary key is, its essential characteristics, and its crucial role in uniquely identifying every single record—or entity instance—in a table. Mastering this concept is essential for ensuring your data is reliable and for building connections between different tables, which we will cover soon.
1. The Need for a Unique Identifier
Before we define a primary key, let's understand why we need one. In the previous lesson, we saw that simply listing attributes isn't enough. If you run a business, you might have multiple customers named 'Steve' or multiple orders for the same product. Relying on attributes like names or product types to find specific records can lead to confusion and errors.
Databases solve this problem by enforcing uniqueness through a special attribute called a key.
Keys in DBMS | Primary Key | How to find it?
To begin, watch this video from the channel "making IT simple". It uses some helpful analogies to introduce the general concept of keys.
Start with the introductory analogy that compares a database key to a real-world key for a lock or an index in a book. This helps build an intuition for its purpose. Continue with the explanation of how a key works in a student table. Pay close attention to why 'Roll Number' is a good identifier, while 'Name' and 'Age' are not.
As the video demonstrates, the core purpose of a key is to provide a reliable way to distinguish one row from every other row in a table.
2. Defining the Primary Key
The most important type of key is the primary key. A primary key is an attribute (or a set of attributes) chosen to uniquely identify each record in a table. To function correctly, a primary key must follow two strict rules.
As the diagram illustrates, a primary key must be:
- Unique: No two rows in a table can have the same primary key value.
- Not Null: The primary key attribute for a record cannot be empty or have a missing value. Every record must have a primary key value.
This rule that a primary key cannot be null is so important that it has its own name in database theory: the entity integrity constraint.
Let's watch a short, clear definition to solidify this.
Keys in DBMS | Primary Key | How to find it?
Return to the "making IT simple" video for a concise definition of the primary key.
Watch the segment from the start of the primary key section. The video explicitly states the two conditions: unique values and no null values.
Here is a simple visual example. In the Student table below, Stu_Id is the primary key. It's unique for every student, and no entry is missing a Stu_Id. The names and ages, however, can have duplicates.
3. Choosing the Right Primary Key
For a given entity, you might find more than one attribute (or group of attributes) that could serve as a primary key because they are unique. These are called candidate keys. The database designer's job is to evaluate the candidate keys and select the most suitable one to be the primary key.
How does a designer make this choice? Let's consider some desirable characteristics of a good primary key:
- It should not change over time.
- It should be simple and concise (e.g., a number is often better than a long text string).
- It must be guaranteed to be unique and never null.
The following video provides an excellent walkthrough of this thought process. The presenter analyzes several attributes for a student entity and evaluates their suitability as a primary key.
Keys in DBMS | Primary Key | How to find it?
This final clip from the "making IT simple" video demonstrates the practical process of selecting a primary key.
Watch the detailed example from where the presenter analyzes a university student table. Notice how attributes like Name, Age, and even Mobile Number are considered and then rejected for logical reasons, until Registration Number is chosen as the best primary key.
This process of elimination is central to good database design. You must think critically about the potential data to ensure the chosen key will always remain unique and non-null under all circumstances.
To formalize this, let's look at the textual definitions.
The Entity Relationship Data Model
This text from "Database Design" by OpenTextBC provides formal definitions for different types of keys.
Read the sections titled Keys, Candidate key, and Primary key. Focus on understanding the relationship between them: you identify all possible candidate keys first, and then select one to be the primary key.
4. Practice Exercise
Now it's time to apply what you've learned. The reading below contains several exercises. Your task is to analyze a table and identify its potential keys.
Exercises from 'The Entity Relationship Data Model'
This resource contains several case studies to test your understanding.
Locate "Question 6" in the exercises section, which refers to the student table shown in "Figure 8.14". Your task is to list all the possible candidate keys for this table and briefly explain why you selected them.
Hint and Solution (Click to expand)
Hint: Look for columns or combinations of columns where every row has a unique value. A good candidate key should also be minimal, meaning you can't remove any attribute from it and still have it be unique.
Solution Walkthrough:
- StudID: This is an excellent candidate key. The values (1234, 5678, etc.) appear to be unique identifiers for each student.
- StudLname: This is not a candidate key. There are two students with the last name 'Jones'.
- StudFname: This is not a candidate key. There are two students named 'John'.
- StudPhone: This is a strong candidate key. All the phone numbers are unique. In a real-world scenario, you might question if a student could have no phone, but based on the data provided, it works.
- Combination (StudFname, StudLname): Let's check this composite key. The pairs are (John, Smith), (Jane, Doe), (Peter, Jones), and (John, Jones). All these combinations are unique. So, (StudFname, StudLname) is another candidate key.
Conclusion for the Exercise:
The candidate keys for the student table in Figure 8.14 are:
StudIDStudPhone{StudFname, StudLname}(as a composite key)
Out of these, StudID is the most common and generally the best choice for a primary key because it's a simple, numeric identifier created specifically for this purpose.
Conclusion
In this lesson, we established the critical role of the primary key. It acts as a unique, non-negotiable identifier for every record in a table, bringing order and integrity to our data.
Here are the main takeaways:
- A primary key (PK) is an attribute whose value uniquely identifies each entity instance (row) in a table.
- It must adhere to two rules: its values must be unique, and it can never be null. This is known as the entity integrity constraint.
- Often, there are multiple candidate keys that could serve as the primary key. The designer chooses the most stable and simple one.
So far, we've focused on using a single attribute as a primary key. But what happens when no single attribute can guarantee uniqueness? In our next lesson, we will address this by defining and using composite primary keys, which are formed by combining two or more attributes.