First of all, let's understand what keys are. 🗝️
Keys are attributes, or combinations of attributes, that uniquely identify each record (row) in a table and help establish relationships between different tables.
There are 7 types of keys in a database that we will discuss here.
Let's create a simple table and understand them one by one.
Student Table ⬇️
| Roll No | Name | Branch | |
|---|---|---|---|
| 101 | Rohit Sharma | ECE | rohits@college.com |
| 102 | Neha | EEE | nehas@college.com |
| 103 | Rohit Mehra | ECE | mehra@college.com |
Here, Roll No and Email are unique, so they can become keys.
Two students cannot have the same Roll No or Email, but they can have the same Name or belong to the same Branch.
For example, if I look into the records and call out Roll No 102, I can immediately pull out Neha's record.
But if I make Name the key and simply call out "Rohit," I will be confused.
Which Rohit am I talking about?
Rohit Sharma or Rohit Mehra?
Now take a look at your 10th marksheet. Your roll number was assigned to uniquely identify you during that examination, while things like your email ID may change over time.
Since the basic concept of keys is clear, let's move on to the different types of keys.
1. Super Key: Any group of columns that can pick out one student
In our table, Roll No can work as a Super Key.
But these combinations can also work:
Roll No + Name
Roll No + Branch
Roll No + Email
Roll No + Branch + Email
Roll No + Branch + Name
Roll No + Branch + Name + Email
all combinations work (even though they are extra).
Any combination that can uniquely find a student is a super key.
2. Candidate Key: The smallest group that can pick out one student
In our table Roll no and Email can be candidate keys, because they are the shortest description to find out a student.
3. Primary Key: The one Candidate Key the teacher picks as the official ID
In our table, we can make Roll No our Primary Key.
But why choose Roll No instead of Email?
Because there are some important rules for choosing a Primary Key.
Rules:
No duplicate values: No two students can share the same value.
Cannot be NULL: Every student must have a value for the Primary Key.
There may be situations where a student does not have an email ID, or their email ID changes, but every student in the school/college database can be assigned a unique Roll No.
That's why Roll No is a better choice for the Primary Key.
Think of it like your Aadhaar number — it is used as a unique identifier for you.
4. Alternate Key: The Candidate Keys we didn't pick as the Primary Key
Remember, we had two Candidate Keys:
Roll No and Email
We selected Roll No as our Primary Key.
So, Email becomes the Alternate Key.
It is still capable of uniquely identifying a student, but it wasn't selected as the official Primary Key.
Think of it as the backup ID.
5. Foreign Key: A column that points to another table's Primary Key
Now imagine we have another table called Branches.
Branches Table
| Branch ID | Branch Name |
|---|---|
| 1 | EEE |
| 2 | ECE |
Instead of storing the Branch Name again and again in our Student Table, we can store the Branch ID.
Our Student Table could look like this:
| Roll No | Name | Branch ID | |
|---|---|---|---|
| 101 | Rohit Sharma | 2 | rohits@college.com |
| 102 | Neha | 1 | nehas@college.com |
| 103 | Rohit Mehra | 2 | mehra@college.com |
Here, Branch ID in the Student Table points to Branch ID in the Branches Table.
So, Branch ID in the Student Table becomes a Foreign Key.
For example:
Branch ID = 1 → EEE
Branch ID = 2 → ECE
It's like a post-it note on your locker that says, "I belong to Room 1."
The Foreign Key connects one table to another.
6. Composite Key: Two or more columns together make a unique ID
Sometimes, one column alone is not enough to uniquely identify a record.
In that case, we can combine two or more columns.
That combination is called a Composite Key.
For example, imagine we have a table where neither Name nor Email alone is sufficient for our requirement, but their combination is unique.
Name + Email
Together, they can identify one student.
That's a Composite Key.
Think of it like saying: The girl in the blue dress*.* Girl alone isn't enough. Blue dress alone may not be enough.
But when you combine both clues, it becomes easier to identify the person.
7. Surrogate Key: A key we create ourselves
Sometimes, the existing columns are not suitable for becoming a Primary Key.
Let's look at this table:
| Name | Branch | CGPA |
|---|---|---|
| Rahul | CSE | 8.5 |
| Rahul | ECE | 8.5 |
| Rishi | CSE | 9.5 |
Here, Name is not unique.
Branch is not unique.
CGPA is also not unique.
Instead of depending on these values, we can add a new column ourselves.
| ID | Name | Branch | CGPA |
|---|---|---|---|
| 1 | Rahul | CSE | 8.5 |
| 2 | Rahul | ECE | 8.5 |
| 3 | Rishi | CSE | 9.5 |
Now we have an ID that uniquely identifies every record.
This ID is called a Surrogate Key.
A Surrogate Key doesn't necessarily have any real-world meaning. It is mainly created to uniquely identify records in the database.
You will often see columns like:
student_id
customer_id
order_id
product_id
These are commonly used as Surrogate Keys.
Quick Cheat Sheet
| Key | One-line explanation |
|---|---|
| Super Key | Any "description" that uniquely finds one student, even if it contains extra information |
| Candidate Key | The shortest possible description that uniquely finds one student |
| Primary Key | The official Candidate Key chosen to identify each record |
| Alternate Key | A Candidate Key that wasn't chosen as the Primary Key |
| Foreign Key | A column that points to a key in another table |
| Composite Key | Two or more columns that work together to uniquely identify a record |
| Surrogate Key | An artificial ID created to uniquely identify records |
Now, take a dataset for and try to find out the keys by yourself.
Happy learning😊
