First, what is a Key?

A key is one or more columns used to identify, relate, or enforce uniqueness in a table.

Think of it as an identity card for data.

Employees Table

Here:

• Employee_ID identifies every employee.
• Email also uniquely identifies every employee.

Both can uniquely identify a row.

Why do we need Keys?

Imagine this table:

Now suppose someone asks:

"Update Rahul's department."

Which Rahul?

The database has no way to know. That's why every table should have a way to uniquely identify each row.

Types of Keys

There are six main keys you'll encounter:

1. Super Key
2. Candidate Key
3. Primary Key
4. Alternate Key
5. Composite Key
6. Foreign Key

Learn them in this order because each builds on the previous one.

1. Super Key

A Super Key is any combination of columns that can uniquely identify a row.

Possible Super Keys:

• Employee_ID
• Email
• Employee_ID + Name
• Employee_ID + Email
• Employee_ID + Email + Name

Some combinations contain extra unnecessary columns.

For example:

Employee_ID + Name

Employee_ID alone is enough. Adding Name does not make it more unique. It still works, but it is unnecessary.

Definition

A Super Key is any set of one or more columns that uniquely identifies each row in a table.

2. Candidate Key

A Candidate Key is the smallest possible Super Key.

It has no unnecessary columns.

Candidate Keys:

• Employee_ID
• Email

Not Candidate Keys:

• Employee_ID + Name
• Email + Name

Because Name is unnecessary.

Easy way to remember

Super Key:

Can uniquely identify.

Candidate Key:

Can uniquely identify using the minimum columns.

Super Key vs Candidate Key

3. Primary Key

Among all Candidate Keys, we choose one to become the Primary Key.

Example:

Candidate Keys:

• Employee_ID
• Email

Choose:

Employee_ID

Now Employee_ID becomes the Primary Key.

Rules of Primary Key

• Must be unique
• Cannot be NULL
• One Primary Key per table
• Used to identify every row

4. Alternate Key

The Candidate Keys not selected as the Primary Key become Alternate Keys.

Example:

Candidate Keys:

• Employee_ID
• Email

Choose Employee_ID as Primary Key.

Then:

Email = Alternate Key

5. Composite Key

A Composite Key consists of two or more columns together that uniquely identify a row.

Neither column alone is sufficient.

Student_Course

Can Student_ID identify a row? No.

Can Course_ID identify a row? No.

But together:

(Student_ID, Course_ID)

identify each enrollment uniquely. That pair is a Composite Key.

6. Foreign Key

A Foreign Key creates a relationship between two tables.

Users

Orders

The User_ID in the Orders table refers to the User_ID in the Users table.

That column is called a Foreign Key.

Users

User_ID (PK)
    ^
    |
Orders
User_ID (FK)

Why Foreign Keys?

Without them, you could accidentally insert an order like:

But User_ID 999 doesn't exist.

With a Foreign Key, the database prevents this and maintains referential integrity.

Constraints

Constraints are rules enforced by the database to maintain valid and consistent data.

PRIMARY KEY Constraint

Ensures:

• Unique
• Not NULL

Duplicate? Not allowed.

NULL? Not allowed.

FOREIGN KEY Constraint

Ensures:

• Referenced row exists
• Invalid relationships are prevented

UNIQUE Constraint

Allows only unique values.

Unlike a Primary Key, a UNIQUE column can typically contain NULL values. Exact behavior depends on the database system.

NOT NULL Constraint

Column cannot be empty.

DEFAULT Constraint

Provides a default value if none is supplied.

Example:

Status = Active

CHECK Constraint

Ensures values satisfy a condition.

Example:

Age >= 18

Trying to insert:

Age = 12

is rejected.

Summary Table

Interview Questions

1. Difference between Super Key and Candidate Key?

A Super Key uniquely identifies a row but may include extra columns. A Candidate Key is a minimal Super Key with no unnecessary columns.

2. Difference between Primary Key and Candidate Key?

A Candidate Key is any minimal unique identifier. A Primary Key is the Candidate Key chosen to uniquely identify rows in the table.

3. Can a table have multiple Candidate Keys?

Yes.

4. Can a table have multiple Primary Keys?

No. A table can have only one Primary Key, though it may consist of multiple columns as a composite primary key.

5. Why do we use Foreign Keys?

To establish relationships between tables and enforce referential integrity by ensuring referenced records exist.

Key Takeaways

• Super Key: any unique identifier, possibly with extra columns.
• Candidate Key: minimal unique identifier.
• Primary Key: the chosen Candidate Key.
• Alternate Key: Candidate Keys not chosen as Primary Key.
• Composite Key: multiple columns together uniquely identify a row.
• Foreign Key: links tables and maintains referential integrity.
• Constraints enforce data quality by preventing invalid or inconsistent data.