Database Management System (DBMS) / Relational Model

Relational Keys

🔑 Relational Keys — Notes

1️⃣ Super Key

A super key is any combination of attributes that can uniquely identify a row — it's allowed to have extra, unnecessary attributes.

✅ Unique ➕ Can include extra columns 🚫 Not necessarily minimal

Example Table: Student

ID

Name

Email

101

Alice

alice@gmail.com

102

Bob

bob@gmail.com

Valid Super Keys:

{ID}
{ID, Name}
{ID, Email}
{ID, Name, Email}
{Email}

2️⃣ Candidate Key

A candidate key is a minimal super key — no extra, unnecessary attributes.

✅ Unique ✅ Minimal ➡️ Every candidate key is a super key, but not every super key is a candidate key

From the table above, the Candidate Keys are:

{ID}
{Email}

(Both uniquely identify a student, and neither has extra baggage.)

3️⃣ Primary Key

The one candidate key chosen to be a table's main identifier.

✅ Unique ✅ Not NULL 🏅 Only one per table

Example:

CREATE TABLE Student (
  ID INT PRIMARY KEY,
  Name VARCHAR(50),
  Email VARCHAR(100)
);

→ {ID} becomes the Primary Key.

4️⃣ Alternate Key

A candidate key that wasn't chosen as the primary key.

✅ Still unique ✅ Still minimal 🔄 Just not the main one

Example:

{ID}    → Primary Key
{Email} → Alternate Key

Often enforced with:

ALTER TABLE Student ADD CONSTRAINT uq_email UNIQUE (Email);

5️⃣ Foreign Key

An attribute in one table that references the primary key of another table — this is what actually links tables together.

🔗 Creates relationships 🚫 Doesn't need to be unique in its own table 🛡️ Enforces referential integrity

Example:

CREATE TABLE Enrolls (
  Student_ID INT,
  Course_ID INT,
  FOREIGN KEY (Student_ID) REFERENCES Student(ID),
  FOREIGN KEY (Course_ID) REFERENCES Course(ID)
);

Student_ID

Course_ID

S001

C101

S001

C102

S002

C103

📌 S001 can repeat — a foreign key allows duplicates.

6️⃣ Composite Key

A key made of two or more attributes, where no single one is enough alone, but together they're unique.

🧩 Multiple columns ✅ Unique only in combination 📐 Describes the shape of a key, not a new category

Example: In the Enrolls table, no single column is unique on its own (Student_ID repeats, Course_ID repeats) — but together:

{Student_ID, Course_ID}

is unique per enrollment record. This composite pair is the Primary Key of Enrolls.

7️⃣ Compound Key

A composite key where each individual attribute is also a foreign key referencing another table.

🧩 Multi-attribute (like composite) 🔗 Each part is a foreign key 🤝 Often used interchangeably with "composite key"

Example:

{Student_ID, Course_ID}

in Enrolls is:

  • Composite → because it's made of 2+ attributes

  • Compound → because Student_ID and Course_ID are each foreign keys pointing back to Student and Course

📊 Quick Summary Table

Key

Unique?

Minimal?

Purpose

🌟 Super Key

✅

❌ maybe not

Any unique identifier

💎 Candidate Key

✅

✅

Minimal unique identifier

🏅 Primary Key

✅

✅

The chosen main identifier

🔄 Alternate Key

✅

✅

Unused candidate key

🔗 Foreign Key

❌

—

Links to another table's PK

🧩 Composite Key

✅ (combined)

✅

Multi-attribute unique key

🤝 Compound Key

✅ (combined)

✅

Composite key made of FKs