Keys in DBMS

A key is an attribute or a set of attributes that is used to uniquely identify tuples rows in a relation table. Keys are fundamental to the relational model — they enforc

What is a Key?

A key is an attribute or a set of attributes that is used to uniquely identify tuples (rows) in a relation (table). Keys are fundamental to the relational model — they enforce entity integrity and enable relationships between tables.

Types of Keys

1. Super Key

A super key is any set of one or more attributes that can uniquely identify a tuple in a relation.

Every super key can identify rows uniquely
May contain extra (unnecessary) attributes
A relation can have many super keys

Employee(EmpID, Aadhaar, Name, Email, Phone) Super Keys: {EmpID} {Aadhaar} {Email} {EmpID, Name} {EmpID, Email} {EmpID, Aadhaar} {EmpID, Name, Email} ... and many more combinations

2. Candidate Key

A candidate key is a minimal super key — a super key from which no attribute can be removed while still preserving uniqueness.

Every attribute in a candidate key is necessary
A table can have multiple candidate keys
All candidate keys are potential choices for primary key

Employee(EmpID, Aadhaar, Name, Email) Candidate Keys: {EmpID}, {Aadhaar}, {Email} Why not {EmpID, Aadhaar}? → It's a super key but NOT a candidate key because {EmpID} alone already uniquely identifies the tuple. Removing Aadhaar doesn't break uniqueness.

3. Primary Key

The primary key is the candidate key that is selected by the database designer to uniquely identify tuples in the table.

Rules:

Must be unique across all rows
Cannot contain NULL values (Entity Integrity Rule)
Only ONE primary key per table
Immutable — should not change over time ideally

sql exampleWoHoTech

CREATE TABLE Student (
    student_id INT PRIMARY KEY,   -- Simple primary key
    name       VARCHAR(100) NOT NULL,
    email      VARCHAR(100) UNIQUE
);

-- Composite Primary Key
CREATE TABLE Enrollment (
    student_id INT,
    course_id  INT,
    grade      CHAR(2),
    PRIMARY KEY (student_id, course_id)  -- Composite PK
);

4. Alternate Key

An alternate key is a candidate key that was NOT chosen as the primary key. Also called a secondary key.

Candidate Keys	{StudentID}, {Email}, {AadhaarNo}
Primary Key	{StudentID}
Alternate Keys	{Email}, {AadhaarNo}

5. Foreign Key

A foreign key is an attribute (or set of attributes) in one table that references the primary key of another table. It enforces referential integrity.

1	Computer Science
2	Mathematics

102	Bob	2
103	Carol	1

Department Table (Referenced)

Employee Table (Referencing)

OR be NULL (if the column is nullable)

Referential Actions:

ON DELETE CASCADE — Delete child rows when parent is deleted
ON DELETE SET NULL — Set FK to NULL when parent is deleted
ON DELETE RESTRICT — Prevent deletion if child rows exist
ON UPDATE CASCADE — Update FK value when PK value changes

6. Composite Key

A composite key is a primary key consisting of two or more attributes. Neither attribute alone is sufficient to uniquely identify a tuple.

OrderItem Table

OrderID	ProductID	Quantity	Price
1001	501	2	500

7. Natural Key vs. Surrogate Key

Type	Description	Example
Natural Key	Key derived from actual data domain	Aadhaar number, ISBN, PAN card
Surrogate Key	System-generated, has no business meaning	Auto-increment integer ID, UUID

When to use surrogate keys:

Natural key is too long (URL, email)
Natural key can change over time
No obvious natural key exists
For performance (integer joins are faster than string joins)

Key Hierarchy Summary

All Possible Attribute Combinations

can uniquely identify

Super Keys

remove unnecessary attributes

Candidate Keys

Primary Key

1 chosen

Alternate Keys

rest

referenced by

Foreign Keys in other tables

Quick Comparison Table

Key Type	Uniqueness	NULL Allowed	Count per Table
Super Key	Yes	Sometimes	Many
Candidate Key	Yes	No	Multiple
Primary Key	Yes	No (enforced)	Exactly 1
Alternate Key	Yes	Usually No	Multiple
Foreign Key	No (can repeat)	Sometimes	Multiple
Composite Key	Yes (combined)	No	1 (when it's the PK)

What is a Key?

Types of Keys

1. Super Key

A super key is any set of one or more attributes that can uniquely identify a tuple in a relation.

Every super key can identify rows uniquely
May contain extra (unnecessary) attributes
A relation can have many super keys

Employee(EmpID, Aadhaar, Name, Email, Phone) Super Keys: {EmpID} {Aadhaar} {Email} {EmpID, Name} {EmpID, Email} {EmpID, Aadhaar} {EmpID, Name, Email} ... and many more combinations

2. Candidate Key

A candidate key is a minimal super key — a super key from which no attribute can be removed while still preserving uniqueness.

Every attribute in a candidate key is necessary
A table can have multiple candidate keys
All candidate keys are potential choices for primary key

3. Primary Key

The primary key is the candidate key that is selected by the database designer to uniquely identify tuples in the table.

Rules:

Must be unique across all rows
Cannot contain NULL values (Entity Integrity Rule)
Only ONE primary key per table
Immutable — should not change over time ideally

sql exampleWoHoTech

CREATE TABLE Student (
    student_id INT PRIMARY KEY,   -- Simple primary key
    name       VARCHAR(100) NOT NULL,
    email      VARCHAR(100) UNIQUE
);

-- Composite Primary Key
CREATE TABLE Enrollment (
    student_id INT,
    course_id  INT,
    grade      CHAR(2),
    PRIMARY KEY (student_id, course_id)  -- Composite PK
);

4. Alternate Key

An alternate key is a candidate key that was NOT chosen as the primary key. Also called a secondary key.

Candidate Keys	{StudentID}, {Email}, {AadhaarNo}
Primary Key	{StudentID}
Alternate Keys	{Email}, {AadhaarNo}

5. Foreign Key

A foreign key is an attribute (or set of attributes) in one table that references the primary key of another table. It enforces referential integrity.

1	Computer Science
2	Mathematics

102	Bob	2
103	Carol	1

Department Table (Referenced)

Employee Table (Referencing)

OR be NULL (if the column is nullable)

Referential Actions:

ON DELETE CASCADE — Delete child rows when parent is deleted
ON DELETE SET NULL — Set FK to NULL when parent is deleted
ON DELETE RESTRICT — Prevent deletion if child rows exist
ON UPDATE CASCADE — Update FK value when PK value changes

6. Composite Key

A composite key is a primary key consisting of two or more attributes. Neither attribute alone is sufficient to uniquely identify a tuple.

OrderItem Table

OrderID	ProductID	Quantity	Price
1001	501	2	500

7. Natural Key vs. Surrogate Key

Type	Description	Example
Natural Key	Key derived from actual data domain	Aadhaar number, ISBN, PAN card
Surrogate Key	System-generated, has no business meaning	Auto-increment integer ID, UUID

When to use surrogate keys:

Natural key is too long (URL, email)
Natural key can change over time
No obvious natural key exists
For performance (integer joins are faster than string joins)

Key Hierarchy Summary

All Possible Attribute Combinations

can uniquely identify

Super Keys

remove unnecessary attributes

Candidate Keys

Primary Key

1 chosen

Alternate Keys

rest

referenced by

Foreign Keys in other tables

Quick Comparison Table

Key Type	Uniqueness	NULL Allowed	Count per Table
Super Key	Yes	Sometimes	Many
Candidate Key	Yes	No	Multiple
Primary Key	Yes	No (enforced)	Exactly 1
Alternate Key	Yes	Usually No	Multiple
Foreign Key	No (can repeat)	Sometimes	Multiple
Composite Key	Yes (combined)	No	1 (when it's the PK)

Keys in DBMS

What is a Key?

Types of Keys

1. Super Key

2. Candidate Key

3. Primary Key

4. Alternate Key

5. Foreign Key

6. Composite Key

OrderItem Table

7. Natural Key vs. Surrogate Key

Key Hierarchy Summary

Quick Comparison Table

Continue learning this concept

Keys in DBMS

What is a Key?

Types of Keys

1. Super Key

2. Candidate Key

3. Primary Key

4. Alternate Key

5. Foreign Key

6. Composite Key

OrderItem Table

7. Natural Key vs. Surrogate Key

Key Hierarchy Summary

Quick Comparison Table

Continue learning this concept