Keys in DBMS: Super Key, Candidate Key, Primary Key, Foreign Key, Alternate Key & Composite Key Explained
Keys in DBMS (Database Management System) are unique identifiers or combinations of attributes within a table that uniquely identify each record. They are crucial for maintaining data integrity and optimizing database operations. Let's explore the importance of keys in DBMS & how they contribute to efficient data handling.
Primary Key:
In the "Employee Table", "Dept_Id" serves as a foreign key referencing the "Dept_Id" primary key in the "Department Table".
Keys in DBMS
A key is an attribute or set of attributes that uniquely identifies any record or tuple from the table.- Key is used to uniquely identify any record or row of data from the table.
- It is also used to establish and identify relationships between tables.
Types of Keys in DBMS
- Super Key
- Candidate Key
- Primary Key
- Alternate Key
- Foreign Key
- Composite Key
1. Super Key
A super key is a combination of all possible attributes that can uniquely identify the rows or tuple in the given relation.- Super key is a superset of a candidate key.
- A table can have many super keys.
- A super key may have additional attribute that are not needed for unique Identity.
| Emp_Id | Name | Aadhar_No | Email_Id | Dept_Id |
|---|---|---|---|---|
| 01 | Amit | 102030405060 | at@gmail.com | 1 |
| 02 | Neeraj | 112233445566 | nj@gmail.com | 2 |
| 03 | Neeraj | 122436486072 | nn@gmail.com | 2 |
| 04 | Vishal | 132639526578 | vl@gmail.com | 3 |
Super Keys:
- {Emp_Id}, {Aadhar_No}, {Email_Id}
- {Emp_Id, Aadhar_No}, {Aadhar_No, Email_Id}, {Emp_Id, Email_Id}
- {Emp_Id, Aadhar_No, Email_Id}, {Emp_Id, Name}, {Emp_Id, Name, Dept_Id}
- {Emp_Id, Name, Aadhar_No, Email_Id, Dept_Id}, etc.....
2. Candidate Key
- A Candidate key is an attribute or set of an attribute which can uniquely identify a tuple.
- A Candidate key is a minimal super key or a Super key with no redundant attributes.
- It is called a minimal super key because we select a candidate key from a set of super key such that selected candidate key is the minimum attribute required to uniquely identify the table.
- Candidate keys are defined as distinct set of attributes from which primary key can be selected.
- Candidate keys are not allowed to have NULL values.
Example : Employee Table
| Emp_Id | Name | Aadhar_No | Email_Id | Dept_Id |
|---|---|---|---|---|
| 01 | Amit | 102030405060 | at@gmail.com | 1 |
| 02 | Neeraj | 112233445566 | nj@gmail.com | 2 |
| 03 | Neeraj | 122436486072 | nn@gmail.com | 2 |
| 04 | Vishal | 132639526578 | vl@gmail.com | 3 |
Candidate Keys:
- {Emp_Id}
- {Aadhar_No}
- {Email_Id}
3. Primary Key
A primary key is on of the candidate key chosen by the database designer to uniquely identify the tuple in the relation.
- The value of primary key can never be NULL.
- The value of primary key must always be unique (not duplicate).
- The values of primary key can never be changed i.e. no updation is possible.
- The value of primary key must be assigned when inserting a record.
- A relation is allowed to have only one primary key.
Example : Employee Table
| Emp_Id | Name | Aadhar_No | Email_Id | Dept_Id |
|---|---|---|---|---|
| 01 | Amit | 102030405060 | at@gmail.com | 1 |
| 02 | Neeraj | 112233445566 | nj@gmail.com | 2 |
| 03 | Neeraj | 122436486072 | nn@gmail.com | 2 |
| 04 | Vishal | 132639526578 | vl@gmail.com | 3 |
- {Emp_Id}
4. Alternate Key
Out of all candidate keys, only one gets selected as primary key, remaining keys are known as alternate keys.
In the Employee table:
- Emp_Id is best suited for the primary key.
- Rest of the attributes like Aadhar_No, Email_Id are considered as a alternate keys.
Example: Employee Table
| Emp_Id | Name | Aadhar_No | Email_Id | Dept_Id |
|---|---|---|---|---|
| 01 | Amit | 102030405060 | at@gmail.com | 1 |
| 02 | Neeraj | 112233445566 | nj@gmail.com | 2 |
| 03 | Neeraj | 122436486072 | nn@gmail.com | 2 |
| 04 | Vishal | 132639526578 | vl@gmail.com | 3 |
Alternate Keys:
- {Aadhar_No}
- {Email_Id}
5. Foreign Key
A Foreign key is an attribute or set of attributes in one table that refers to the Primary key in another table.
- A foreign key is used to link two tables together.
- The purpose of the foreign key is to ensure or maintain referential integrity of the data.
- Foreign key can take only those values which are present in the primary key of the referenced relation.
- Foreign key may have a name other than that of a primary key.
- Foreign key can take the NULL value.
- There is no restriction on a foreign key to be unique.
- In fact, foreign key is not unique most of the time.
Example: Consider two tables: "Employee Table" and "Department Table"
Employee Table
| Emp_Id | Name | Aadhar_No | Email_Id | Dept_Id |
|---|---|---|---|---|
| 01 | Amit | 102030405060 | at@gmail.com | 1 |
| 02 | Neeraj | 112233445566 | nj@gmail.com | 2 |
| 03 | Neeraj | 122436486072 | nn@gmail.com | 2 |
| 04 | Vishal | 132639526578 | vl@gmail.com | 3 |
Department Table
| Dept_Id | Dept_Name |
|---|---|
| 1 | Sales |
| 2 | Marketing |
| 3 | HR |
Foreign Key:
in Employee Table
- Dept_Id
Here "Employee Table" is known as the Referencing relation while the "Department Table" is known as the Referenced relation.
- Referenced relation may also be called as the master table or primary table.
- Referencing relation may also be called as the foreign table.
6. Composite Key
A composite key is a combination of two or more attributes that uniquely identify a tuple. It is also known as Compound Key.
- Unlike a primary key, each attribute within a composite key may not be unique individually, but the combination is unique.
- This is particularly useful when a single attribute is not sufficient to ensure uniqueness, requiring a combination of attributes for identification.
Example: Let's consider a table of "Order Details"
| Order_Id | Product_Id | Quantity |
|---|---|---|
| 1 | 101 | 2 |
| 1 | 102 | 1 |
| 2 | 103 | 3 |
Here in this table, a composite key might be a combination of "Order_Id" and "Product_Id", as one order can contain multiple products, but the combination of Order_Id and Product_Id will be unique for each order line.
Composite Key:
- {Order_Id, Product_Id}
Conclusion
In conclusion, keys in DBMS are fundamental for data integrity, relationship establishment, and efficient data retrieval. They define unique identifiers within tables, enforce relationships between tables, and optimize database performance. Keys play a critical role in relational database design, ensuring accurate data management and retrieval operations.
