Keys in DBMS: Primary, Candidate, Super, Foreign

In a relational database, keys are essential elements that help uniquely identify records and define relationships between tables. They ensure data integrity, uniqueness, and help in query optimization.

1. Primary Key

• A Primary Key is a column or a combination of columns that uniquely identifies each row in a table.
• It cannot contain NULL values and must always have unique values.
• Every table can have only one primary key.

Example:

Here, RollNo is the primary key. No two students can have the same RollNo.

2. Candidate Key

• A Candidate Key is a set of attributes that can uniquely identify a record in a table.
• A table may have multiple candidate keys, but one of them is selected as the primary key.
• Candidate keys must be unique and not NULL.

Example:

In a Student table:

Both RollNo and Email are candidate keys if each is unique for every student.

3. Super Key

• A Super Key is any combination of columns that uniquely identifies a row in a table.
• It may contain extra attributes that are not necessary for uniqueness.
• All candidate keys and the primary key are super keys, but not all super keys are candidate keys.

Example:

In the Student table:

All of the above can be super keys if they ensure uniqueness.

4. 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 two tables.
• It can accept NULL values and duplicate values (if not part of a composite primary key).

Example:

Here, DeptID in Student is a foreign key pointing to DeptID in Department.

Summary Table

Understanding keys is crucial for database normalization, enforcing constraints, and maintaining data integrity. Each type of key serves a unique purpose in ensuring reliable data design and relationship management in DBMS.