Different types of Keys in DBMS (Database Management)

Reading Time: 4 minutes


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.
E-10Arun Kumararun89@gmail.com02-Jan-8911-Jan-17Engineering
E-20Hempreet Singhhempreets21@gmail.com20-Oct-8808-Aug-17Accounting
E-30Amit Guptaamits21@gmail.com22-Dec-8811-Jan-17Engineering
E-40Kiranjeetiamkd20@gmail.com02-Jan-8920-Jul-17Corporate Support
E-50Arun Kumararunkmr9@gmail.com20-Oct-8808-Aug-17Accounting
Table: Employee Table

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 NoReg_NoNameCourse
1RE101Vinay KumarB.Tech
2RE102Sumit GaurB.Tech
3RE103Pawan SharmaBCA
4RE104Mohd. ZainMCA
Table: Student Table
101RE101Vinay Kumar
109RE104Mohd. Zain
Table: Hostel Table

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:
1Rajay Gangwar22MaleDelhi
2Vikas Yadav22MaleDelhi
3Sumit Bhatia22MaleDelhi
4Vikas Yadav22MaleDelhi
5Vinay Singh22MaleDelhi
Table: Anonymous Table

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.

Written by 

Kuldeep is a Software Consultant at Knoldus Software LLP. He has a sound knowledge of various programming languages like C, C++, Java, MySQL, and various frameworks like Apache Kafka and Spring/Springboot. He is passionate about daily and continuous improvement.