Introduction
A huge amount of data is available in this real-world. Now, for storing the data in DBMS, a large number of tables are required. These tables may contain thousand of duplicate, sorted, and unsorted Records. Now, to fetch any particular or specific record, without any constraints/ restrictions from these tables is a very difficult process. To overcome all the difficulties, a new concept of Keys arose. Keys ensure that there are no rows or tuples with duplicate records. Let’s understand the concept of Keys in DBMS.
What is a Key?
- Keys play an important role while creating a table.
- Keys are used to identifying a row(tuple) in a relation(table).
- They may have a single attribute (Example:
Emp_ID
), or a group of attributes. (Example:Name
,DOB
) - Mostly used for establishing the relationships among the various tables.
Emp_ID | Name | Email_ID | DOB | DOJ | Department |
E-10 | Arun Kumar | arun89@gmail.com | 02-Jan-89 | 11-Jan-17 | Engineering |
E-20 | Hempreet Singh | hempreets21@gmail.com | 20-Oct-88 | 08-Aug-17 | Accounting |
E-30 | Amit Gupta | amits21@gmail.com | 22-Dec-88 | 11-Jan-17 | Engineering |
E-40 | Kiranjeet | iamkd20@gmail.com | 02-Jan-89 | 20-Jul-17 | Corporate Support |
E-50 | Arun Kumar | arunkmr9@gmail.com | 20-Oct-88 | 08-Aug-17 | Accounting |
Example: In Employee Table (Emp_ID
, Name
, DOB
, Email_ID
, DOJ
, Department
):- Emp_ID, Email_ID
, and (Name, DOB
) are keys since they are unique for each Employee.
Types of Keys in DBMS
- Super keys
- Candidate keys
- Primary keys
- Alternate/ Secondary keys
- Foreign keys
- Composite keys
- Surrogate keys
Super Key:
- Might have a single key attribute (Example:
Emp_ID
), or a group of multiple keys (Example:Name
,DOB
) that can uniquely identify tuples in a table. - These keys may have redundant attributes along with a key attribute. Example: (
Emp_ID
) can have various unnecessary attributes like: (Emp_ID
,DOB
), (Emp_ID
,Name
), (Emp_ID
,DOJ
), (Emp_ID
,DOB
,DOJ
,Department
), (Emp_ID
,DOB
,Name
), etc. - Consists of redundant attributes that might not be important for identifying tuples.
- Example: In Employee Table
Emp_ID
,
- (
Emp_ID
,Name
,DOJ
), - (
Email_ID
,DOB
,Department
) … etc. can all be Super keys. As (Emp_ID
) and (Email_ID
) are necessary attributes (unique for each Employee), the rest other attributes are unnecessary attributes.
- Real_Life Examples:
- PAN No + Name
- Employee ID + Name
- Aadhar No + DOB + Name
Candidate Key:
- Minimal Super Key is termed as the Candidate Key.
- Candidate keys should always be unique, can have NULL values.
- Have a single attribute (Example:
Emp_ID
), or a group of attributes. (Example: (Name
,DOB
) - A Table may have one or more Candidate Keys
- (
Emp_ID
), (Email_ID
),(Name, DOJ),
(Name, Department),
(DOJ, Department), (Name, Department)
… etc. all are the Candidate keys of the Employee Table.
- (
- Every Candidate key is a Super Key, vice-versa is not possible (as Super key is the addition of 0,1 or more attributes to a Candidate key)
Emp_ID
– Candidate Key, Super Key- (
Emp_ID, Name
) – Super Key, Not Candidate Key
- Real-Life Examples:
- Aadhar No
- PAN No
- Account No
- Voter ID
- Employee ID
- Passport No
- Driving Licence
- IMEI No
Primary Key:
- The best suitable Candidate key acts as the Primary key of the Table. (Example:
Emp_ID
) - It is chosen by the D.B.A. (Database Administrator).
- A Table can have a single Primary key (If
Emp_ID
is Primary key, then other Candidate keys can’t be the Primary key of the same table). - The primary key should always be unique and non-null key, can’t have NULL values.
- Every Primary key is the candidate key
- A Candidate key may or may not be a Primary Key.
- (
Emp_ID
) – Candidate Key, Primary Key - (
Email_ID
) – Not Primary Key, Candidate Key - (
Name
,DOB
) – Candidate Key, Not Primary Key
- (
Alternate Key:
- The Keys other than Primary Key are called Alternate Keys (If
Emp_ID
is Primary key, then other Candidate keys are Called the Alternate Keys). - Alternate(Secondary) Keys can be made Primary Key.
- Example: (
Email_ID
), (DOB
,Name
), (DOB
,DOJ
,Department
), (DOJ
,Name
), etc all are Alternate Keys. - Sometimes Secondary/Alternate key is required for the indexing, for better and faster searching.
Foreign Key:
- It helps us in establishing relationships with other tables.
- Foreign Key points to the Primary Key of another table (Example:
Reg_No
of Hostel Table is the Primary Key of the Student Table)
Roll No | Reg_No | Name | Course |
1 | RE101 | Vinay Kumar | B.Tech |
2 | RE102 | Sumit Gaur | B.Tech |
3 | RE103 | Pawan Sharma | BCA |
4 | RE104 | Mohd. Zain | MCA |
Room_No | Reg_No | Name |
101 | RE101 | Vinay Kumar |
109 | RE104 | Mohd. Zain |
The Above example tries to showcase that, Hostel will be allocated to those students who are studying in that college. Reference of the Reg_No
is taken to verify the availability in that college.
Composite Key:
- Sometimes single attribute fails to uniquely identify the rows/tuples in a table.
- Composite Key is a combination of two or more attributes that uniquely identify rows in a table. (Example: Name, DOB)
- Always have two or more attributes.
- Real-Life Example:
- Vehicle No (UP 16 AC 1001)
- State_ID = UP
- District_ID = 16
- Vehicle_No = AC 1001
- Name (Raj Kumar Sharma)
- First_Name = Raj
- Middle_Name = Kumar
- Last_Name = Sharma
- Vehicle No (UP 16 AC 1001)
Surrogate Key:
- This key is an artificial key that can distinctly identify every row in the table.
- Surrogate Keys of DBMS are allowed in certain cases like
- The primary key is too big,
- complicated Primary Key
- Absence of key.
- It is unique, updatable, and can’t be NULL.
- Example:
SR_No. | Name | Age | Gender | City |
1 | Rajay Gangwar | 22 | Male | Delhi |
2 | Vikas Yadav | 22 | Male | Delhi |
3 | Sumit Bhatia | 22 | Male | Delhi |
4 | Vikas Yadav | 22 | Male | Delhi |
5 | Vinay Singh | 22 | Male | Delhi |
Above Anonymous Table doesn’t have any primary key. Thus, SR_No
. is used as the Surrogate key. SR_No
. is generated at the time of the insertion of the records. It is updatable.
- Real-Life Example:
- SR No
- Phone No
- Room No
- Batch No
- Shifts (like Morning Shift, Evening Shift, etc.)
I hope you liked the blog and was able to understand the concept of Keys of DBMS in Details.