Different types of Keys in DBMS (Database Management)

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

1. Super keys
2. Candidate keys
3. Primary keys
4. Alternate/ Secondary keys
5. Foreign keys
6. Composite keys
7. 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

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.