Keys in DBMS

⏱ Estimated reading time: 3 min

A Key in DBMS is an attribute or a set of attributes that helps uniquely identify a record (tuple) in a table. Keys ensure uniqueness, establish relationships between tables, and maintain the integrity of the database. Keys also help in indexing, searching, and organizing data efficiently.

Keys are fundamental to the Relational Model because they enforce constraints that keep the data accurate, consistent, and anomaly-free.

Types of Keys in DBMS

1️⃣ Primary Key

A primary key uniquely identifies each record in a table.

• Cannot be NULL

• Cannot have duplicate values

• At most one primary key per table

Example:
Student(RollNo, Name, Course)
Here, RollNo can be the primary key.

2️⃣ Candidate Key

Candidate keys are all possible keys that can uniquely identify a record.
From multiple candidate keys, one is chosen as the primary key.

Example:
Employee(EmpID, Email, Mobile)
Here, EmpID, Email, and Mobile are candidate keys.

3️⃣ Super Key

A super key is any combination of attributes that can uniquely identify a tuple.
Every candidate key is a super key, but every super key is not a candidate key.

Example:
{RollNo}, {RollNo, Name}, {RollNo, Course} are all super keys.

4️⃣ Composite Key

A composite key is a combination of two or more attributes used to uniquely identify a record.

Example:
Enrollment(StudentID, CourseID, Semester)
Here, (StudentID, CourseID) together can form a composite key.

5️⃣ Foreign Key

A foreign key is an attribute in one table that refers to the primary key of another table.
It is used to establish relationships between tables and enforce referential integrity.

Example:
Orders(CustomerID) → refers to Customer(CustomerID)

6️⃣ Alternate Key

All candidate keys except the primary key are known as alternate keys.

Example:
If EmpID is the primary key, then Email and Mobile become alternate keys.

7️⃣ Unique Key

Unique key ensures that all values in a column are distinct, but unlike primary key, it can contain NULL (once).

Example:
Email column in a user table.

8️⃣ Surrogate Key

A surrogate key is an artificially created key, usually an auto-increment number, used when no natural primary key exists.

Example:
OrderID = 1,2,3,… automatically generated.

9️⃣ Secondary Key

Used for searching records based on non-unique attributes.

Example:
Searching students by Course, Department, or City.

Summary Table

Conclusion

Keys play an essential role in identifying, organizing, and establishing relationships between data in a database. They ensure uniqueness, avoid redundancy, and maintain consistency through constraints. Understanding the different types of keys is crucial for database design, relational modeling, and SQL operations.