DBMS Keys, Constraints & Functional Dependency in Hindi (Complete Guide)

A Key is an attribute or a set of attributes that uniquely identifies a record (tuple) in a table.

Definition

Key वह Attribute (या Attributes का समूह) है जो Table के प्रत्येक Record (Row) को Unique रूप से पहचानता है।

Simple Definition

Key = Identity Card of a Record

जैसे हर व्यक्ति का Aadhaar Number अलग होता है, वैसे ही Database में हर Record को पहचानने के लिए Key का उपयोग किया जाता है।

---

Why Do We Need Keys?

Suppose we have the following Student table:

RollNo	Name	Branch
101	Rahul	CSE
102	Aman	IT
103	Rahul	ECE

Question:

How can we identify Rahul?

There are two students named Rahul.

But RollNo is unique.

Therefore,

RollNo becomes the Key.

---

Objectives of Keys

Keys help us:

• Identify each record uniquely.
• Prevent duplicate records.
• Create relationships between tables.
• Maintain data integrity.
• Improve searching speed.
• Improve database performance.

---

Characteristics of a Good Key

A good key should:

✔ Be unique.

✔ Never be NULL (for Primary Key).

✔ Be stable (should not change frequently).

✔ Be short and simple.

✔ Be meaningful if possible.

---

Types of Keys

There are many types of keys.  

                    KEYS

                      │
 ┌────────────┬─────────────┬─────────────┐
 │            │             │             │
Super      Candidate     Primary     Alternate
 │
 ├─────────────┬─────────────┐
 │             │             │
Composite   Foreign      Unique
 │
 ├─────────────┬─────────────┐
 │             │             │
Natural    Surrogate     Secondary

We will study every key in detail.

---

Super Key

Definition

A Super Key is a set of one or more attributes that can uniquely identify a record.

Example

Student Table

RollNo	Name	Email	Phone
101	Rahul	rahul@gmail.com	9876543210
102	Aman	aman@gmail.com	9876543211

Possible Super Keys:

• RollNo
• Email
• Phone
• RollNo + Name
• RollNo + Email
• RollNo + Phone
• Email + Name

All these combinations uniquely identify the student.

---

Important Point

A Super Key may contain extra (unnecessary) attributes.

Example:

RollNo + Name

RollNo alone is enough.

Name is extra.

Still, it is a Super Key.

---

Candidate Key

Definition

A Candidate Key is the minimum Super Key.

It contains no unnecessary attributes.

Example

Student Table

RollNo	Email	Name
101	rahul@gmail.com	Rahul
102	aman@gmail.com	Aman

Possible Super Keys:

• RollNo ✔
• Email ✔
• RollNo + Name ✘
• Email + Name ✘

Candidate Keys:

• RollNo
• Email

---

Difference

Super Key:

RollNo + Name

Candidate Key:

RollNo

---

 Primary Key

Definition

The Candidate Key selected to identify records uniquely is called the Primary Key.

Only one Primary Key can exist in a table.

---

Example

Student Table

RollNo	Name	Email
101	Rahul	rahul@gmail.com
102	Aman	aman@gmail.com

Primary Key:

RollNo

---

Rules of Primary Key

• Must be unique.
• Cannot be NULL.
• Only one Primary Key per table.
• Should not change frequently.

---

SQL Example

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

---

Alternate Key

Definition

Candidate Keys that are not selected as the Primary Key are called Alternate Keys.

Example

Candidate Keys:

• RollNo
• Email

Primary Key:

RollNo

Alternate Key:

Email

---

Composite Key (Compound Key)

Definition

A key made using two or more attributes is called a Composite Key.

Example

Enrollment Table

StudentID	CourseID
101	C101
101	C102
102	C101

Neither StudentID nor CourseID alone is unique.

But together:

StudentID + CourseID

uniquely identifies each record.

---

SQL Example

CREATE TABLE Enrollment
(
    StudentID INT,
    CourseID VARCHAR(10),
    PRIMARY KEY(StudentID, CourseID)
);

 Foreign Key

Definition

A Foreign Key is an attribute that refers to the Primary Key of another table.

It creates a relationship between two tables.

---

Example

Student Table

StudentID	Name
101	Rahul
102	Aman

Primary Key = StudentID

---

Enrollment Table

StudentID	Course
101	DBMS
102	OS

Here,

StudentID in Enrollment is a Foreign Key.

---

Diagram

Student
----------------
StudentID (PK)
Name

        │
        │
        ▼

Enrollment
----------------
StudentID (FK)
Course

---

Why Foreign Keys?

• Connect tables.
• Prevent invalid data.
• Maintain Referential Integrity.

---

SQL Example

CREATE TABLE Enrollment
(
    StudentID INT,
    Course VARCHAR(50),
    FOREIGN KEY(StudentID)
    REFERENCES Student(StudentID)
);

Unique Key

Definition

A Unique Key ensures that all values are unique.

Unlike a Primary Key:

• Multiple Unique Keys can exist.
• Depending on the DBMS, a Unique Key may allow NULL values (for example, many SQL systems allow one or more NULLs under UNIQUE rules).

---

Example

RollNo	Email
101	rahul@gmail.com
102	aman@gmail.com

Email can be a Unique Key.

---

 Natural Key

Definition

A Natural Key is a real-world attribute that naturally identifies a record.

Examples:

• Aadhaar Number
• Passport Number
• Email ID
• PAN Number

These values already exist in the real world.

---

 Surrogate Key

Definition

A Surrogate Key is an artificial key generated by the system.

Example

EmployeeID = 1001
EmployeeID = 1002
EmployeeID = 1003

These IDs have no business meaning but uniquely identify records.

---

Natural Key vs Surrogate Key

Natural Key	Surrogate Key
Real-world value	Artificial value
Has business meaning	No business meaning
May change	Usually never changes
Example: Email	Example: Auto-increment ID

---

 Secondary Key

A Secondary Key is used for searching records but does not uniquely identify them.

Example

Roll	Name
101	Rahul
102	Rahul
103	Aman

Searching by Name is useful, but Name is not unique.

---

Complete Comparison of Keys

Key Type	Unique?	NULL Allowed?	Main Purpose
Super Key	Yes	Depends	Uniquely identifies records (may include extra attributes)
Candidate Key	Yes	No	Minimal unique identifier
Primary Key	Yes	No	Main identifier for the table
Alternate Key	Yes	No	Candidate key not chosen as Primary Key
Composite Key	Yes	No	Uses multiple attributes together
Foreign Key	Not necessarily	Yes (unless restricted)	Links one table to another
Unique Key	Yes	DBMS-dependent	Prevents duplicate values
Natural Key	Usually	Depends	Real-world identifier
Surrogate Key	Yes	No	System-generated identifier
Secondary Key	No	Yes	Searching and indexing

---

Real-Life Example (Bank Database)

Customer Table

CustomerID	Name	Email
101	Rahul	rahul@gmail.com

• Primary Key → CustomerID
• Alternate Key → Email

---

Account Table

AccountNo	CustomerID	Balance
5001	101	50000

• Primary Key → AccountNo
• Foreign Key → CustomerID

Relationship:

Customer
-------------------
CustomerID (PK)

        │
        │
        ▼

Account
-------------------
AccountNo (PK)

CustomerID (FK)

---

Memory Trick

Super Key
     │
     ▼
Candidate Key
     │
     ▼
Primary Key
     │
     ▼
Foreign Key (used in another table)