What is a Key in DBMS?
In DBMS, a key is an attribute or set of attributes used to uniquely identify a record in a table.
Keys are very important because:
- They prevent duplicate data
- They maintain data integrity
- They help in connecting tables
1. Candidate Key
A Candidate Key is a minimal set of attributes that can uniquely identify a record in a table.
🧠 Simple Understanding
A candidate key is a super key with no extra attributes. If we remove any attribute from it, it will lose uniqueness.
⚡ Key Points
- ✔ Must uniquely identify each row
- ✔ Must be minimal (no unnecessary attributes)
- ✔ There can be multiple candidate keys
- ❌ Cannot have extra attributes
📊 Example Table
| STUD_NO | NAME | PHONE |
|---|---|---|
| 101 | Amit | 9876 |
| 102 | Ravi | 9123 |
🔍 Analysis
- STUD_NO → ✅ Unique → Candidate Key
- PHONE → ✅ Unique → Candidate Key (if no duplicates)
- (STUD_NO, NAME) → ❌ Not minimal
- ✔ STUD_NO
- ✔ PHONE (if unique)
❗ Important Concept (Minimality)
A key is called minimal if removing any attribute makes it NOT unique.
Example:
- (STUD_NO, NAME) → Remove NAME → still unique
- So it is NOT minimal ❌
🤯 Confusion Example (Combination Case)
| NAME | PHONE |
|---|---|
| Amit | 9876 |
| Amit | 9123 |
| Ravi | 9876 |
🔍 Analysis
- NAME → ❌ Not unique
- PHONE → ❌ Not unique
- (NAME, PHONE) → ✅ Unique
- ✔ It is unique
- ✔ It is minimal (cannot remove anything)
🎯 GATE Point
✔ Candidate Key = Minimal Super Key
✔ There can be multiple candidate keys in a table
💣 Golden Rule
If a key has extra attributes → Not a Candidate Key
2. Super Key
A Super Key is a set of one or more attributes that can uniquely identify a record in a table.
🧠 Simple Understanding
Even if extra attributes are added, the key is still valid as long as uniqueness is maintained.
⚡ Key Points
- ✔ Can be single or multiple attributes
- ✔ Must uniquely identify each row
- ✔ May contain extra attributes
- ❌ Not minimal
📊 Example Table
| STUD_NO | NAME | PHONE |
|---|---|---|
| 101 | Amit | 9876 |
| 102 | Ravi | 9123 |
✅ Possible Super Keys
- STUD_NO
- (STUD_NO, NAME)
- (STUD_NO, PHONE)
❗ Important Concept
If one attribute is already unique, adding more attributes will still keep it unique.
🎯 GATE Point
✔ Every Candidate Key is a Super Key
❌ But every Super Key is NOT a Candidate Key
🔥 Understanding Uniqueness (Most Important Concept)
In DBMS, before deciding any key, we must understand uniqueness.
❌ Case 1: Not Unique
| NAME | PHONE |
|---|---|
| Amit | 9876 |
| Ravi | 9876 |
PHONE is repeated → ❌ Not Unique → Not a key
❓ Why Phone Number Can Repeat?
Many students think that phone numbers are always unique, but in databases this is NOT always true.
- 👨👩👧 Family members can share the same phone number
- 📞 One number can be used as an emergency contact for multiple people
- 📝 Data entry mistakes can create duplicates
- 🔄 Users may change numbers, but old records remain
- ❓ Phone number may not be mandatory (can be NULL or reused)
❌ Case 2: Not Unique
| NAME | PHONE |
|---|---|
| Amit | 9876 |
| Amit | 9123 |
NAME is repeated → ❌ Not Unique
✅ Case 3: Unique
| NAME | PHONE |
|---|---|
| Amit | 9876 |
| Ravi | 9123 |
Both NAME and PHONE are unique → ✅ Can be keys
🤯 Case 4: Combination Becomes Unique
| NAME | PHONE |
|---|---|
| Amit | 9876 |
| Amit | 9123 |
| Ravi | 9876 |
🔍 Analysis
- NAME → ❌ Not unique
- PHONE → ❌ Not unique
- (NAME, PHONE) → ✅ Unique
- ✔ Super Key
- ✔ Candidate Key (because minimal)
🧠 How to Check Uniqueness (Exam Method)
- Pick a column
- Check if values repeat
- If repeat → Not unique
- If not repeat → Unique
- If not unique → try combinations
💣 Golden Rule
If even one duplicate exists → Not unique
3. Primary Key
A Primary Key is a candidate key that is selected to uniquely identify each record in a table.
🧠 Simple Understanding
There can be multiple candidate keys in a table, but only one is chosen as the primary key.
⚡ Key Points
- ✔ Must be unique
- ✔ Cannot be NULL
- ✔ Only one primary key per table
- ✔ Selected from candidate keys
📊 Example Table
| STUD_NO | PHONE | |
|---|---|---|
| 101 | amit@gmail.com | 9876 |
| 102 | ravi@gmail.com | 9123 |
🔍 Analysis
- STUD_NO → Candidate Key
- EMAIL → Candidate Key
- PHONE → Candidate Key (if unique)
- ✔ STUD_NO → Primary Key
❗ Important Concept
A primary key is chosen based on reliability and simplicity.
- STUD_NO → stable and unique ✅
- PHONE → may change ❌
🤯 Confusion Example
If multiple candidate keys exist, which one becomes primary key?
- ✔ Stable (does not change)
- ✔ Simple
- ✔ Reliable
🎯 GATE Point
✔ Primary Key is always a Candidate Key
✔ But not all Candidate Keys are Primary Keys
💣 Golden Rule
Primary Key cannot be NULL and must be unique.
4. Foreign Key
A Foreign Key is an attribute in one table that is used to link it with another table.
🧠 Simple Understanding
A foreign key in one table refers to the primary key of another table.
⚡ Key Points
- ✔ Used to connect two tables
- ✔ Refers to Primary Key of another table
- ✔ Can have duplicate values
- ✔ Can have NULL values
📊 Example (Two Tables)
Table 1: STUDENT
| STUD_ID (PK) | NAME |
|---|---|
| 101 | Amit |
| 102 | Ravi |
Table 2: COURSE
| COURSE_ID | STUD_ID (FK) |
|---|---|
| C1 | 101 |
| C2 | 102 |
🔍 Analysis
- STUD_ID in STUDENT → Primary Key
- STUD_ID in COURSE → Foreign Key
❗ Important Concept (Referential Integrity)
A foreign key must match a value in the parent table (or be NULL).
- ✔ STUD_ID = 101 → exists → valid
- ❌ STUD_ID = 999 → does not exist → invalid
🤯 Confusion Point
Can foreign key have duplicates?
🎯 GATE Point
✔ Foreign Key can have duplicates
✔ Foreign Key can be NULL
✔ Maintains relationship between tables
💣 Golden Rule
Foreign Key must match a Primary Key value (or be NULL)
5. Composite Key
A Composite Key is a key formed by combining two or more attributes to uniquely identify a record.
🧠 Simple Understanding
When a single column is not enough to uniquely identify records, we combine multiple columns to form a composite key.
⚡ Key Points
- ✔ Made of two or more attributes
- ✔ Used when single column is not unique
- ✔ Ensures uniqueness using combination
📊 Example Table
| STUD_ID | COURSE_ID | MARKS |
|---|---|---|
| 101 | C1 | 85 |
| 101 | C2 | 90 |
| 102 | C1 | 88 |
🔍 Analysis
- STUD_ID → ❌ Not unique
- COURSE_ID → ❌ Not unique
- (STUD_ID, COURSE_ID) → ✅ Unique
❗ Important Concept
Composite Key is used when no single attribute can uniquely identify a record.
🎯 GATE Point
✔ Composite Key can also be a Candidate Key
✔ It can be selected as Primary Key
💣 Golden Rule
If single attributes fail → try combination → Composite Key
Difference Between Keys
- Primary Key: Selected key
- Candidate Key: All possible keys
- Super Key: Candidate key + extra attributes
- Foreign Key: Links tables
- Composite Key: Combination of columns
📊 Comparison of Keys
| Key Type | Unique | Minimal | NULL Allowed |
|---|---|---|---|
| Super Key | ✔ | ❌ | ✔ |
| Candidate Key | ✔ | ✔ | ❌ |
| Primary Key | ✔ | ✔ | ❌ |
| Foreign Key | ❌ | ❌ | ✔ |
| Composite Key | ✔ | ✔ | ❌ |
GATE Important Points
- Primary key = Unique + Not NULL
- Candidate key = Minimal super key
- Foreign key ensures referential integrity
- Super key may contain redundant attributes
Conclusion
DBMS keys are the foundation of database design. Understanding keys is very important before studying Functional Dependency and Normalization.