Types of Keys in DBMS (Complete Guide with Examples)

By

Introduction

Keys are very important in a Database Management System (DBMS). They help uniquely identify records, create relationships between tables, and maintain data integrity. Without keys, managing data accurately would be very difficult.

In this guide, you will learn the main types of keys in DBMS with simple examples.

 

What is a Key in DBMS?

A key is one or more attributes (columns) used to identify records in a table and establish relationships between tables.

Keys ensure that data in the database remains unique, consistent, and properly connected.

 

Main Types of Keys in DBMS

The most important keys are:

  1. Primary Key
  2. Foreign Key
  3. Candidate Key
  4. Super Key
  5. Alternate Key
  6. Composite Key
  7. Unique Key (optional but useful to know)

Let’s understand each one.

 

01. Primary Key

A Primary Key uniquely identifies each record in a table.

Features

  • Must be unique
  • Cannot be NULL
  • Only one primary key per table

Example

Students table

StudentID (PK)

Name

101

Ali

102

Sara

Here, StudentID is the Primary Key.

 

02. Foreign Key

A Foreign Key is a column that refers to the Primary Key of another table. It creates relationships between tables.

Features

  • Can contain duplicates
  • Can contain NULL (usually)
  • Maintains referential integrity

Example

Orders table

OrderID

StudentID (FK)

1

101

2

102

StudentID here is a Foreign Key.

 

03. Candidate Key

A Candidate Key is any column (or set of columns) that can uniquely identify a record.

From candidate keys, one is selected as the Primary Key.

Example

Students table

StudentID

NIC

Email

Possible candidate keys:

  • StudentID
  • NIC
  • Email

Any of these could uniquely identify a student.

04. Super Key

A Super Key is any set of one or more columns that can uniquely identify a record.

It may contain extra unnecessary attributes.

Example

If StudentID alone is unique, then these are super keys:

  • StudentID
  • StudentID + Name
  • StudentID + Email

All can uniquely identify the record.

 

05. Alternate Key

An Alternate Key is a candidate key that was not selected as the primary key.

Example

If:

  • StudentID → chosen as Primary Key
  • NIC → remaining candidate key

Then NIC becomes an Alternate Key.

 

06. Composite Key

A Composite Key is a primary key made of two or more columns together.

Used when a single column cannot uniquely identify records.

Example

Enrollment table

StudentID

CourseID

Grade

Here, the combination of:

  • StudentID + CourseID

forms the Composite Key.

 

07. Unique Key

A Unique Key ensures that all values in a column are unique, similar to a primary key.

Difference from Primary Key

  • Can allow one NULL value (in many DBMS)
  • A table can have multiple unique keys

Example

Email column in a Users table.

 

Summary Table

Key Type

Purpose

Primary Key

Uniquely identifies records

Foreign Key

Creates relationships

Candidate Key

Possible primary keys

Super Key

Any unique identifier set

Alternate Key

Candidate key not chosen

Composite Key

Multiple columns as key

Unique Key

Ensures column uniqueness

 

Conclusion

Keys are essential in DBMS for uniquely identifying records and maintaining relationships between tables. Understanding different types of keys helps in designing efficient and reliable databases. Every database designer and IT student must clearly understand these key concepts.




Comments

Post a Comment

Popular posts from this blog

DBMS Normalization (1NF, 2NF, 3NF) — Easy Guide with Examples

By
Image
Introduction Normalization in DBMS is a process used to organize data in a database to reduce redundancy and improve data integrity. It divides large tables into smaller, related tables and removes data duplication. Normalization is performed in steps called Normal Forms : First Normal Form (1NF) Second Normal Form (2NF) Third Normal Form (3NF) Let’s understand each in a simple way.   What is Normalization? Normalization is the process of structuring a database to: Remove data redundancy Avoid update anomalies Improve data consistency Maintain data integrity It is mainly used in Relational Database Management Systems (RDBMS) .   🔹 First Normal Form (1NF) Rule of 1NF A table is in First Normal Form (1NF) if: Each field contains atomic (single) values No repeating groups or multivalued attributes Each record is unique   Example (Not in 1NF) Students table StudentID Name ...
Read more

DATA BASE MANAGEMENT SYSTEMS (DBMS)

By
Image
DATA BASE MANAGEMENT SYSTEMS (DBMS) Data Data are raw facts or figures about people, objects, or events that can be stored and processed by a computer. By themselves, data usually have little meaning until they are organized or interpreted. Example: Student name = "Ali", Age = 21. Metadata Metadata are “data about data.” They describe the structure, meaning, or properties of the actual data, helping systems understand how to store, manage, and use the data. Example: In a Students table — Column name: Age Data type: INTEGER Size: 3 In simple terms: Data = the actual values Metadata = information that describes those values Information Information is processed, organized, and meaningful data that is useful for decision-making. When raw data is interpreted and given context, it becomes information. Example: Data: Student marks = 85, 90, 78 Information: The student’s average mark is 84.3 and the student has passed.  In simple terms: Data ...
Read more

The Importance of Learning English

By
Image
Unlocking Opportunities: The Importance of Learning English In today's interconnected world, proficiency in English has become more than just a valuable skill—it's a gateway to limitless opportunities. Whether you're aiming to advance your career, broaden your horizons, or connect with people from diverse cultures, mastering the English language opens doors that may have otherwise remained closed. Global Communication: English serves as the lingua franca of the modern world, facilitating communication across borders and bridging cultural divides. From business negotiations and academic collaborations to international travel and social interactions, English proficiency empowers individuals to navigate diverse contexts with ease and confidence. Career Advancement: In the global marketplace, fluency in English is often a prerequisite for career success. Many multinational corporations conduct business in English, and proficiency in the language can significantly enhance job ...
Read more