Databse Keys: Primary, Foreign, Candidate, Composite, and Super Key

In a relational database, keys are special columns used to uniquely identify records (rows) in a table and to create links between different tables. They are crucial for ensuring data integrity and for building relationships between data.

Let’s use a simple Students table to understand each type of key.

Students Table Example:

StudentID	AdmissionNo	AadharNo	FirstName	LastName
101	2025A	1111…	Rahul	Sharma
102	2025B	2222…	Priya	Gupta
103	2025C	3333…	Amit	Singh

---

Super Key

A Super Key is any column or a combination of columns whose values can uniquely identify a row in a table.

• It’s the most general type of key.
• A table can have many super keys.
• Example:
  • {StudentID} is a super key (it’s unique).
  • {AdmissionNo} is a super key.
  • {AadharNo} is a super key.
  • {StudentID, FirstName} is also a super key (even though FirstName is not needed, the combination is still unique).
  • {AdmissionNo, FirstName, LastName} is also a super key.

---

Candidate Key

A Candidate Key is a minimal super key. It’s a super key with no redundant (unnecessary) columns. It’s a “candidate” to become the primary key.

• It must uniquely identify a row.
• It must contain no extra columns.
• Example: From the super keys listed above, the minimal ones are:
  • {StudentID}
  • {AdmissionNo}
  • {AadharNo} These are all candidate keys because you can’t remove any column from them and still have a unique identifier. {StudentID, FirstName} is not a candidate key because FirstName is redundant.

---

Primary Key

The Primary Key is the specific candidate key that the database designer chooses to be the main identifier for the table.

• It must be unique for each row.
• It cannot contain NULL (empty) values.
• There can be only one primary key per table.
• Example: A designer would choose one of the candidate keys to be the primary key. Let’s choose StudentID. It’s the most common choice as it’s a simple, unique number created specifically to identify students.

---

Composite Key

A Composite Key is a key that consists of two or more columns combined to uniquely identify a row. It is used when a single column is not enough to create a unique identifier.

• It’s a type of primary key or candidate key that has multiple columns.
• Example: Consider a new Enrollment table.

CourseID	StudentID	Grade
C10	101	A
C12	101	B
C10	102	A

Here, CourseID alone is not unique, and StudentID alone is not unique. But the combination {CourseID, StudentID} is unique and would serve as the composite primary key.

---

Foreign Key

A Foreign Key is a column or a set of columns in one table that refers to the primary key of another table. It’s used to link two tables together.

• It establishes a relationship between tables.
• The value of a foreign key must match an existing value in the primary key of the referenced table.
• Example: Let’s use the Enrollment table again.
  • The StudentID column in the Enrollment table is a foreign key.
  • It refers to the primary key (StudentID) of the Students table.
  • This link ensures that you cannot enroll a student in a course if that student does not exist in the Students table.

• X
• Instagram
• WhatsApp