Primary Key, Foreign Key, and Index

 

What is Primary Key, Foreign Key, and Index in MySQL?

Databases are the backbone of every modern application from banking systems and e-commerce platforms to social media apps. If you’re learning MySQL, understanding Primary Keys, Foreign Keys, and Indexes is absolutely essential.

These three concepts:

• Maintain data integrity

• Improve query performance

• Build proper relationships between tables

• Prevent duplicate and inconsistent data

Whether you’re a student, developer, DBA, or preparing for interviews, mastering these fundamentals will make you confident in database design and optimization.

Let’s break everything down step by step.

---

What is a Primary Key in MySQL?

Definition

A Primary Key is a column (or combination of columns) that uniquely identifies each record in a table.

Key Characteristics:

• Must contain unique values

• Cannot contain NULL values

• Only one primary key per table

• Automatically creates a clustered index (in InnoDB)

---

Why Do We Need a Primary Key?

Imagine a student database with multiple students having the same name.

How do you uniquely identify each student?

That’s where a Primary Key comes in.

---

Example: Creating a Primary Key

Step 1: Create Table with Primary Key

CREATE TABLE students (
student_id INT PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(100)
);

Here:

• student_id uniquely identifies each student.

---

Step 2: Insert Data

INSERT INTO students VALUES (1, 'Rahul', 'rahul@email.com');
INSERT INTO students VALUES (2, 'Anjali', 'anjali@email.com');

---

What Happens If You Insert Duplicate?

INSERT INTO students VALUES (1, 'Amit', 'amit@email.com');

Result:

ERROR 1062 (23000): Duplicate entry ‘1’ for key ‘PRIMARY’

MySQL prevents duplicate primary key values.

---

Composite Primary Key (Multiple Columns)

Sometimes a single column is not enough.

CREATE TABLE enrollments (
student_id INT,
course_id INT,
PRIMARY KEY (student_id, course_id)
);

Here:

• Combination of student_id and course_id must be unique.

---

What is a Foreign Key in MySQL?

Definition

A Foreign Key is a column in one table that references the Primary Key of another table.

It creates a relationship between two tables.

---

Why Do We Need a Foreign Key?

To maintain referential integrity.

Example:

• A student enrolls in a course.

• You should not allow enrollment for a student who doesn’t exist.

---

Step-by-Step Example

Step 1: Create Parent Table

CREATE TABLE departments (
dept_id INT PRIMARY KEY,
dept_name VARCHAR(100)
);

---

Step 2: Create Child Table with Foreign Key

CREATE TABLE employees (
emp_id INT PRIMARY KEY,
emp_name VARCHAR(100),
dept_id INT,
FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

Here:

• dept_id in employees references dept_id in departments.

---

 What Happens If You Insert Invalid Data?

INSERT INTO employees VALUES (1, 'Amit', 10);

If department 10 doesn’t exist:

MySQL will reject the insert.

---

Foreign Key with ON DELETE / ON UPDATE

You can control behavior:

FOREIGN KEY (dept_id)
REFERENCES departments(dept_id)
ON DELETE CASCADE
ON UPDATE CASCADE;

Options:

• CASCADE → Automatically updates/deletes child records

• SET NULL → Sets foreign key to NULL

• RESTRICT → Prevents deletion

---

Visual Representation

Parent Table	Child Table
departments	employees
dept_id (PK)	dept_id (FK)

---

What is an Index in MySQL?

Definition

An Index is a database object that improves the speed of data retrieval operations.

Think of it like:
Index page of a book

Instead of scanning the entire book, you go directly to the page number.

---

Why Do We Need an Index?

Without an index:

• MySQL performs Full Table Scan

• Slow performance on large tables

With an index:

• Faster SELECT queries

• Efficient searching and filtering

---

Example Without Index

SELECT * FROM students WHERE email = 'rahul@email.com';

If there is no index:

• MySQL scans all rows.

---

Creating an Index

CREATE INDEX idx_email ON students(email);

Now:

• MySQL quickly finds matching records.

---

Types of Indexes in MySQL

Index Type	Description
PRIMARY	Automatically created for Primary Key
UNIQUE	Ensures unique values
INDEX	Normal index
FULLTEXT	For text searching
COMPOSITE	Index on multiple columns

---

Example: Unique Index

CREATE UNIQUE INDEX idx_unique_email 
ON students(email);

Now duplicate emails are not allowed.

---

Primary Key vs Foreign Key vs Index

Feature	Primary Key	Foreign Key	Index
Ensures uniqueness	✅ Yes	❌ No	❌ (unless UNIQUE)
Allows NULL	❌ No	✅ Yes	✅ Yes
Creates relationship	❌ No	✅ Yes	❌ No
Improves performance	✅ Yes	❌ No	✅ Yes
One per table	✅ Yes	❌ Multiple allowed	❌ Multiple allowed

---

Real-World Scenario Example

Let’s say you’re designing an e-commerce database:

• users → user_id (Primary Key)

• orders → order_id (Primary Key)

• orders.user_id → Foreign Key referencing users

• Index on orders.created_at → Faster order history search

This structure ensures:

• No duplicate users

• No invalid orders

• Fast search performance

---

Best Practices

For Primary Key

• Use INT AUTO_INCREMENT

• Keep it short and numeric

• Avoid using long strings

For Foreign Key

• Ensure same data type as parent key

• Use proper cascading rules

For Index

• Index columns used in:

  • WHERE

  • JOIN

  • ORDER BY

• Avoid too many indexes (slows INSERT/UPDATE)

---

Final Thoughts

Understanding Primary Keys, Foreign Keys, and Indexes is the foundation of database design.

• Primary Key → Uniquely identifies records

• Foreign Key → Builds relationships

• Index → Improves performance

If you master these three concepts, you’ll be able to design scalable and optimized databases confidently.

Whether you’re preparing for interviews, working on real-time projects, or managing production systems these fundamentals will always matter.

If you’re looking to build strong fundamentals in MySQL and other database technologies, Learnomate Technologies provides industry-oriented training designed for students, working professionals, and aspiring DBAs. From understanding core concepts like Primary Keys, Foreign Keys, and Indexes to mastering real-time database administration and performance tuning, Learnomate focuses on practical learning, mock interviews, and hands-on projects to ensure you are job-ready and confident in real-world environments.

MySQL

Share: