Keys in DBMS: Primary, Candidate, Super & Foreign Key Explained

What Problem Do Keys Solve?

Imagine a college database with 10,000 students. Two students can have the same name. Two students can be in the same department. Even birthdates can match.

So how does the database find exactly one record? The answer is a key.

Keys solve four real problems:

• Identification: find one exact row among millions

• Uniqueness: stop duplicate entries

• Relationships: connect tables like Student and Enrollment

• Integrity: block invalid or incomplete data

Without keys, a database is just a pile of rows where nothing can be trusted.

The Table We Will Use

Let’s fix one simple table in our mind and use it for every key type. This keeps the whole topic connected.

In this table, Student_ID is different for every row. Roll_No is also different. Email is also different. But Name and Dept repeat.

Keep this observation in mind. Every key type below comes from this single table.

Super Key: Any Set That Identifies Rows

A super key is any attribute or group of attributes that can uniquely identify a row.

From our table, these are all super keys:

• {Student_ID}

• {Roll_No}

• {Email}

• {Student_ID, Name}

• {Roll_No, Dept}

• {Student_ID, Roll_No, Email}

Notice the pattern: a super key is allowed to carry extra attributes. Even if you add Name or Dept to Student_ID, the set still identifies rows uniquely, so it remains a super key.

Super key = uniqueness guaranteed, extra attributes allowed.

Candidate Key: The Minimal Super Key

A candidate key is a super key with no extra attributes. If you remove any attribute from it, it stops being unique.

From our table, the candidate keys are:

• {Student_ID}

• {Roll_No}

• {Email}

Why is {Student_ID, Name} not a candidate key? Because even after removing Name, Student_ID alone still identifies rows. The extra attribute disqualifies it.

Candidate key = super key minus extra weight.

A simple test for exams:

• Does it identify rows uniquely? If no → not even a super key.

• Can any attribute be removed and still stay unique? If yes → super key only. If no → candidate key.

Primary Key: The Chosen Candidate Key

A table can have several candidate keys, but only one becomes the primary key. It is the candidate key selected by the database designer.

In our table, we choose Student_ID as the primary key.

Rules of a primary key:

• Values must be unique

• Values cannot be NULL

• Only one primary key per table

• It should be stable and short where possible

Why did we choose Student_ID and not Email? Both are unique, but emails can change when a student updates a personal account. IDs rarely change. Stability matters.

Alternate Key: The Candidates That Were Not Chosen

After choosing Student_ID as the primary key, the remaining candidate keys become alternate keys.

• {Roll_No} → alternate key

• {Email} → alternate key

Alternate key = candidate key − primary key.

This term is small, but examiners love it because it checks whether you understand the full family of keys, not just the primary key.

Composite Key: A Key Made of Multiple Columns

When a single column cannot identify a row, we combine columns. A key with two or more attributes is called a composite key.

Remember the Enrollment table from our normalization guide? A student can take many courses, and a course can have many students. So neither Student_ID nor Course alone is unique. But the pair is:

(Student_ID, Course) → composite primary key

Composite keys matter a lot in 2NF questions, because partial dependency appears only when the key is composite.

Foreign Key: The Bridge Between Tables

A foreign key is a column in one table that refers to the primary key of another table.

Example: in the Enrollment table, Student_ID is a foreign key that points to Student(Student_ID).

This protection is called referential integrity. The database refuses to store an enrollment for a student who does not exist.

Important facts about foreign keys:

• A foreign key can contain NULL in many designs (for example, an unassigned record)

• A foreign key can repeat (many enrollments for the same student)

• It always points to a primary key or unique key in the parent table

The Key Family in One View

Until here, the keys were easy because we could “see” the table. But exams do not give you a filled table. They give you attributes and functional dependencies, and ask: find the candidate keys. That exact exam skill is covered in the next part with a full solved method.

How to Find Candidate Keys From Functional Dependencies

In exams, you are rarely given a filled table. You are given attributes and functional dependencies (FDs), and asked to find the candidate keys. This is the exact method, step by step.

First, one simple definition:

Closure of a set = all attributes you can reach using the FDs, starting from that set.

If the closure of a set covers all attributes of the table, that set is a super key. If it is also minimal, it is a candidate key.

The Solved Example

Let the table be R(A, B, C, D) with these functional dependencies:

• AB → C

• C → D

• D → A

Step 1: Find the “must-have” attributes.

Look at the right side of all FDs: C, D, and A appear there. But B never appears on the right side. That means no FD can produce B. So B must be present in every candidate key.

Step 2: Test the closure of B alone.

(B)+ = {B}. It does not cover all attributes, so B alone is not a key. We must add one more attribute.

Step 3: Test B with each remaining attribute.

Step 4: Check minimality.

No single attribute gives the full closure: (A)+ = {A}, (B)+ = {B}, (C)+ = {A,C,D}, (D)+ = {A,D}. So nothing can be removed from AB, BC, or BD.

Final answer: the candidate keys are AB, BC, and BD.

Prime and Non-Prime Attributes

An attribute is prime if it is part of any candidate key. Otherwise it is non-prime.

In our example, the candidate keys are AB, BC, BD. So A, B, C, and D all appear in some candidate key. Here, every attribute is prime.

This term matters because 2NF and 3NF definitions use it, and interviewers ask it directly.

Prime = member of the candidate key family. Non-prime = outsider.

Primary Key vs Unique Key: The Interview Question

This is one of the most asked DBMS interview questions. The clean answer is a comparison, not a paragraph.

Super Key vs Candidate Key vs Primary Key vs Alternate Key

How to Count Super Keys: The Exam Counting Problem

Some exam questions ask: “How many super keys are possible?” Here is the simple case.

Let a table be R(A, B, C) and let the only candidate key be {A}. Then every super key must contain A. So list all subsets that contain A:

• {A}

• {A, B}

• {A, C}

• {A, B, C}

Total super keys = 4. The shortcut for this simple case is 2 raised to the power (total attributes − key attributes), here 2² = 4.

Keys in Real SQL

Now see how all of this becomes real database code. The first tab creates the design. The second tab shows what the database rejects.

CREATE TABLE student (
  student_id INT PRIMARY KEY,
  roll_no VARCHAR(10) UNIQUE,
  email VARCHAR(50) UNIQUE,
  name VARCHAR(50),
  dept VARCHAR(10)
);

CREATE TABLE enrollment (
  student_id INT,
  course_id VARCHAR(10),
  grade CHAR(1),
  PRIMARY KEY (student_id, course_id),
  FOREIGN KEY (student_id)
    REFERENCES student(student_id)
    ON DELETE CASCADE
);

-- fails: duplicate primary key
INSERT INTO student
VALUES (101, 'CS-21', 'riya@apnoai.com', 'Riya', 'CS');
INSERT INTO student
VALUES (101, 'CS-22', 'aman@apnoai.com', 'Aman', 'CS');

-- fails: foreign key violation
INSERT INTO enrollment
VALUES (999, 'DBMS', 'A');

-- fails: primary key can never be NULL
INSERT INTO student
VALUES (NULL, 'EC-11', 'tara@apnoai.com', 'Tara', 'EC');

Notice three things: the composite primary key in enrollment, the foreign key protecting referential integrity, and ON DELETE CASCADE, which deletes a student’s enrollments automatically when the student row is deleted.

Common Exam Mistakes With Keys

Interview Questions Students Actually Face

1. Can a primary key be NULL?

No. A primary key can never contain NULL. That is a hard rule.

2. Can a foreign key be NULL?

Yes, in many designs. For example, an order may exist before a delivery partner is assigned. But a non-NULL foreign key value must exist in the parent table.

3. Is every candidate key a super key?

Yes. Every candidate key is a super key, but not every super key is a candidate key, because super keys can carry extra attributes.

4. Why choose an ID over email as the primary key?

Because emails can change and can be long. A primary key should be stable, short, and never NULL.

5. What is a composite key and when is it needed?

A key made of two or more columns. It is needed when no single column can identify a row, like (Student_ID, Course) in an enrollment table.

6. How do you find candidate keys from FDs?

Find attributes that never appear on the right side, include them in every key, compute closures, and keep only minimal sets that cover all attributes.

Conclusion

Keys are not a memorization topic. They are a family with one job: keeping every row identifiable, connected, and trustworthy.

Once you can move from super key to candidate key to primary key, and once you can find candidate keys from functional dependencies using closure, no exam question on keys can surprise you.

Practice the closure method on three or four different FD sets, and revise the memory lines before the exam. That is the difference between reading about keys and actually owning them.