October 1, 2026

MySQL Constraints & Indexes: PRIMARY KEY, UNIQUE, NOT NULL, DEFAULT & INDEX – Complete Beginner Guide

Introduction

MySQL database mein sirf data store karna enough nahi hota. Humein ye bhi ensure karna hota hai ki database mein correct, valid aur consistent data hi store ho.

For example:

• User ka email duplicate nahi hona chahiye.
• Har user ka ek unique id hona chahiye.
• User ka name empty nahi hona chahiye.
• Agar user registration ke time status nahi deta, to default status active ho sakta hai.
• Database mein frequently search hone wale data ko quickly find karne ke liye INDEX use kiya ja sakta hai.

MySQL mein in requirements ko handle karne ke liye hum Constraints aur Indexes ka use karte hain.

Is tutorial mein hum beginner-friendly way mein samjhenge:

• PRIMARY KEY
• UNIQUE
• NOT NULL
• DEFAULT
• INDEX
• Constraints vs Indexes
• Multiple constraints ek hi table mein
• Composite Index
• Index create/drop karna
• Real-world examples
• Common mistakes
• Best practices
• Practice exercises

---

1. MySQL Constraints Kya Hote Hain?

Constraints database ke rules hote hain jo define karte hain ki table mein kis type ka data allowed hai.

Simple words mein:

Constraint = Database ke data ke liye rule.

Example:

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE
);

Yahan:

• PRIMARY KEY → id unique hona chahiye.
• NOT NULL → name empty/NULL nahi ho sakta.
• UNIQUE → same email dobara use nahi ho sakta.

---

2. Constraints Ki Zarurat Kyu Hoti Hai?

Suppose humare paas ek users table hai.

Without constraints, database mein accidentally ye data aa sakta hai:

id	name	email
1	Rahul	rahul@gmail.com
1	Amit	amit@gmail.com
3	Priya	rahul@gmail.com

Yahan problems hain:

• id = 1 duplicate hai.
• Same email do users ke paas hai.
• Database ki data integrity compromise ho sakti hai.

Constraints in problems ko prevent karne mein help karte hain.

---

3. MySQL Common Constraints

MySQL mein commonly used constraints:

Constraint	Purpose
PRIMARY KEY	Row ko uniquely identify karta hai
UNIQUE	Duplicate values prevent karta hai
NOT NULL	NULL value prevent karta hai
DEFAULT	Value na dene par default value set karta hai
FOREIGN KEY	Tables ke beech relationship maintain karta hai
CHECK	Condition ke according data validate karta hai

Is tutorial mein hum mainly PRIMARY KEY, UNIQUE, NOT NULL, DEFAULT aur INDEX par focus karenge.

---

4. PRIMARY KEY Kya Hai?

PRIMARY KEY table ke har record ko uniquely identify karti hai.

Example:

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(150)
);

Yahan id primary key hai.

Valid data:

INSERT INTO users VALUES
(1, 'Rahul', 'rahul@gmail.com');

INSERT INTO users VALUES
(2, 'Amit', 'amit@gmail.com');

Lekin ye allowed nahi hoga:

INSERT INTO users VALUES
(1, 'Priya', 'priya@gmail.com');

Kyunki id = 1 already exist karta hai.

---

5. PRIMARY KEY Ki Important Properties

PRIMARY KEY:

• Duplicate value allow nahi karti.
• NULL value allow nahi karti.
• Ek table mein normally ek primary key constraint hota hai.
• Primary key automatically indexed hoti hai.
• Single-column ya multiple-column ho sakti hai.

Example:

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    price DECIMAL(10,2)
);

---

6. AUTO_INCREMENT Ke Saath PRIMARY KEY

Real-world applications mein IDs manually enter karna practical nahi hota.

Isliye commonly:

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    email VARCHAR(150)
);

Ab insert karte time id dene ki zarurat nahi:

INSERT INTO users (name, email)
VALUES ('Rahul', 'rahul@gmail.com');

Next record:

INSERT INTO users (name, email)
VALUES ('Amit', 'amit@gmail.com');

Database automatically IDs generate karega.

Expected result:

id	name	email
1	Rahul	rahul@gmail.com
2	Amit	amit@gmail.com

---

7. UNIQUE Constraint Kya Hai?

UNIQUE constraint ensure karta hai ki column mein duplicate values na aayein.

Example:

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    email VARCHAR(150) UNIQUE
);

Ab:

INSERT INTO users (name, email)
VALUES ('Rahul', 'rahul@gmail.com');

Ye valid hai.

Lekin:

INSERT INTO users (name, email)
VALUES ('Amit', 'rahul@gmail.com');

Ye fail hoga because email already exist karta hai.

---

8. PRIMARY KEY vs UNIQUE

Dono duplicate values ko prevent karte hain, lekin dono ka purpose same nahi hai.

Feature	PRIMARY KEY	UNIQUE
Duplicate values	Not allowed	Not allowed
NULL	Not allowed	MySQL mein NULL handling alag hoti hai; multiple NULLs possible ho sakte hain
Table mein count	Normally one primary key	Multiple UNIQUE constraints ho sakte hain
Main purpose	Record identification	Value uniqueness
Automatically indexed	Yes	Yes

Simple example:

id       → PRIMARY KEY
email    → UNIQUE
username → UNIQUE

---

9. NOT NULL Constraint Kya Hai?

NOT NULL ka matlab hai ki column mein NULL value allowed nahi hogi.

Example:

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL
);

Ab:

INSERT INTO users (name, email)
VALUES ('Rahul', 'rahul@gmail.com');

Valid hai.

Lekin:

INSERT INTO users (name, email)
VALUES (NULL, 'rahul@gmail.com');

Ye error dega because name is NOT NULL.

---

10. NOT NULL vs Empty String

Beginner ke liye ye difference samajhna important hai.

NULL aur empty string '' same nahi hain.

Example:

name = NULL

Means:

Value unknown/missing hai.

Whereas:

name = ''

Means:

Value ek empty string hai.

Isliye NOT NULL ka matlab ye nahi ki user automatically non-empty text hi dega. Agar business rule mein empty string bhi prevent karni hai, to application validation ya suitable CHECK condition bhi consider karni chahiye.

---

11. DEFAULT Constraint Kya Hai?

DEFAULT ka use tab hota hai jab INSERT ke time user koi value provide nahi karta.

Example:

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    status VARCHAR(20) DEFAULT 'active'
);

Ab:

INSERT INTO users (name)
VALUES ('Rahul');

status provide nahi kiya gaya.

Database automatically:

active

store karega.

Expected result:

id	name	status
1	Rahul	active

---

12. DEFAULT Ka Real-Time Example

E-commerce website mein order ka status automatically:

pending

ho sakta hai.

Example:

CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_name VARCHAR(100) NOT NULL,
    status VARCHAR(20) DEFAULT 'pending'
);

Insert:

INSERT INTO orders (customer_name)
VALUES ('Rahul');

Result:

order_id	customer_name	status
1	Rahul	pending

Later status update ho sakta hai:

UPDATE orders
SET status = 'shipped'
WHERE order_id = 1;

---

13. Multiple Constraints Ek Hi Column Par

Ek column par multiple constraints use kiye ja sakte hain.

Example:

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    status VARCHAR(20) NOT NULL DEFAULT 'active'
);

Yahan:

username

NOT NULL
UNIQUE

Matlab:

• username missing nahi ho sakta.
• duplicate username allowed nahi hai.

status

NOT NULL
DEFAULT 'active'

Matlab:

• status NULL nahi hona chahiye.
• value na dene par active automatically set hoga.

---

14. Complete Users Table Example

Ab ek practical table banate hain:

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(150) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'active'
);

Insert:

INSERT INTO users (username, email, name)
VALUES
('rahul123', 'rahul@gmail.com', 'Rahul'),
('amit123', 'amit@gmail.com', 'Amit'),
('priya123', 'priya@gmail.com', 'Priya');

Expected result:

id	username	email	name	status
1	rahul123	rahul@gmail.com	Rahul	active
2	amit123	amit@gmail.com	Amit	active
3	priya123	priya@gmail.com	Priya	active

---

15. INDEX Kya Hai?

Ab aate hain INDEX par.

Index ka main purpose hai database ko data quickly find karne mein help karna.

Real-world example:

Suppose ek book mein 1000 pages hain aur aapko "MySQL" word find karna hai.

Agar book mein index diya hua hai, to aap directly relevant page par ja sakte ho.

Database index bhi similar concept follow karta hai.

Simple words:

INDEX = Data ko efficiently search karne mein help karne wali database structure.

---

16. INDEX Ki Zarurat Kyu Hoti Hai?

Suppose users table mein:

1,000,000 users

hain.

Aur query hai:

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

Agar suitable index nahi hai, database ko potentially bahut saare rows examine karne pad sakte hain.

Agar email par suitable index hai, MySQL lookup ko significantly more efficient bana sakta hai.

Example:

CREATE INDEX idx_users_email
ON users(email);

---

17. INDEX Create Karne Ka Syntax

Basic syntax:

CREATE INDEX index_name
ON table_name(column_name);

Example:

CREATE INDEX idx_users_name
ON users(name);

Ab name column ke liye index create ho gaya.

---

18. UNIQUE INDEX

Unique values enforce karne ke liye UNIQUE constraint use kar sakte hain:

CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(150) UNIQUE
);

MySQL is uniqueness ko enforce karne ke liye index structure use karta hai.

Existing table mein:

ALTER TABLE users
ADD UNIQUE (email);

Ya:

CREATE UNIQUE INDEX idx_unique_email
ON users(email);

---

19. INDEX vs UNIQUE

Normal index aur unique index mein important difference hai.

Normal INDEX

CREATE INDEX idx_name
ON users(name);

Purpose:

Search/query performance improve karna.

Duplicate values allowed hain.

For example:

Rahul
Rahul
Amit
Priya
Rahul

allowed ho sakta hai.

UNIQUE INDEX

CREATE UNIQUE INDEX idx_email
ON users(email);

Purpose:

Uniqueness enforce karna.

Duplicate values allowed nahi hoti, subject to MySQL's NULL semantics.

---

20. PRIMARY KEY Automatically Indexed Hoti Hai

Agar hum likhte hain:

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

To id ke liye separate normal index create karne ki zarurat nahi hoti.

Primary key already indexed hoti hai.

Isliye ye karna unnecessary hai:

CREATE INDEX idx_id
ON users(id);

---

21. UNIQUE Column Bhi Indexed Hota Hai

Example:

CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(150) UNIQUE
);

email ki uniqueness enforce karne ke liye index structure maintain hota hai.

Isliye same column par unnecessary duplicate index create karne se bachna chahiye.

---

22. INDEX With WHERE Query

Suppose:

CREATE INDEX idx_users_status
ON users(status);

Ab query:

SELECT *
FROM users
WHERE status = 'active';

Database optimizer available indexes ko consider kar sakta hai.

Lekin important point:

Har query automatically index use karegi, aisa assume nahi karna chahiye.

MySQL optimizer query, table size, data distribution, statistics aur available indexes ke basis par execution plan choose karta hai.

---

23. INDEX With ORDER BY

Suppose frequently query hoti hai:

SELECT *
FROM users
ORDER BY name;

name par suitable index kuch workloads mein sorting/read efficiency improve kar sakta hai.

Example:

CREATE INDEX idx_users_name
ON users(name);

Lekin index design hamesha actual query patterns ke according karna chahiye.

---

24. Composite INDEX Kya Hai?

Jab hum ek index mein multiple columns use karte hain, usse Composite Index kehte hain.

Example:

CREATE INDEX idx_user_status_name
ON users(status, name);

Yahan index do columns par hai:

status
name

---

25. Composite Index Ka Real-Time Example

Suppose e-commerce application mein query frequently run hoti hai:

SELECT *
FROM orders
WHERE customer_id = 10
AND status = 'pending';

Possible index:

CREATE INDEX idx_orders_customer_status
ON orders(customer_id, status);

Is type ka composite index query ko efficiently support kar sakta hai.

---

26. Composite Index Mein Column Order Important Hai

Ye bahut important concept hai.

Example:

CREATE INDEX idx_orders_customer_status
ON orders(customer_id, status);

Iska column order:

customer_id → status

hai.

Index design karte waqt query patterns ko dekhna zaroori hai.

For example:

WHERE customer_id = 10

aur:

WHERE customer_id = 10
AND status = 'pending'

index ke liye relevant patterns hain.

Lekin:

WHERE status = 'pending'

ke liye same composite index ka benefit query optimizer aur data distribution par depend karega; first-column rule ko basic guideline ke roop mein samjhein.

---

27. INDEX Remove Kaise Karein?

Agar kisi index ki zarurat nahi hai, use remove kar sakte hain.

Syntax:

DROP INDEX index_name
ON table_name;

Example:

DROP INDEX idx_users_name
ON users;

---

28. Table Ke Indexes Kaise Dekhein?

Use:

SHOW INDEX FROM users;

Ye table ke indexes ke baare mein information show karega.

Example:

SHOW INDEX FROM users;

Aapko primary key, unique indexes aur other indexes ki information mil sakti hai.

---

29. Table Structure Kaise Check Karein?

Use:

DESCRIBE users;

Ya:

DESC users;

Example:

DESC users;

Isse columns, datatype, NULL allowance, key information aur default values dekhne mein help milti hai.

---

30. Constraints Add Karna Using ALTER TABLE

Agar table pehle se exist karta hai, to ALTER TABLE se constraints add kar sakte hain.

Example:

ALTER TABLE users
ADD UNIQUE (email);

Primary key:

ALTER TABLE users
ADD PRIMARY KEY (id);

NOT NULL:

ALTER TABLE users
MODIFY name VARCHAR(100) NOT NULL;

Default:

ALTER TABLE users
MODIFY status VARCHAR(20) DEFAULT 'active';

---

31. Real-Time Example: Registration System

Suppose hum PHP/CodeIgniter 4 application bana rahe hain.

Users table:

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(150) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Yahan database level par multiple rules implement ho rahe hain:

Column	Rule
id	PRIMARY KEY + AUTO_INCREMENT
username	NOT NULL + UNIQUE
email	NOT NULL + UNIQUE
password	NOT NULL
status	NOT NULL + DEFAULT
created_at	DEFAULT current timestamp

Ye real-world application database ka common pattern hai.

---

32. Real-Time Example: E-Commerce Products

Products table:

CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(150) NOT NULL,
    sku VARCHAR(50) NOT NULL UNIQUE,
    price DECIMAL(10,2) NOT NULL,
    stock INT NOT NULL DEFAULT 0,
    status VARCHAR(20) NOT NULL DEFAULT 'active'
);

Yahan:

product_id

PRIMARY KEY

Har product ko uniquely identify karega.

sku

UNIQUE

Same SKU do products ko nahi diya ja sakta.

product_name

NOT NULL

Product ka naam required hai.

stock

DEFAULT 0

Agar stock provide nahi kiya, to default 0.

status

DEFAULT 'active'

New product automatically active ho sakta hai.

---

33. Product Search Ke Liye INDEX

Suppose application mein frequently search hota hai:

SELECT *
FROM products
WHERE product_name = 'Laptop';

To suitable index consider kiya ja sakta hai:

CREATE INDEX idx_product_name
ON products(product_name);

Agar SKU already UNIQUE hai, to uske liye additional normal index generally unnecessary hai because uniqueness enforce karne ke liye index already maintain hota hai.

---

34. Foreign Key Aur Index

Foreign key bhi database design ka important constraint hai.

Example:

CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    order_date DATE,
    FOREIGN KEY (user_id)
        REFERENCES users(id)
);

Yahan:

users.id

parent table ka key hai aur:

orders.user_id

child table ka foreign key hai.

InnoDB foreign-key columns ke liye required indexing ko handle karta hai, lekin schema design karte waqt foreign-key indexing aur query performance ko samajhna important hai.

---

35. CHECK Constraint – Bonus Concept

Although hamara main focus PRIMARY KEY, UNIQUE, NOT NULL, DEFAULT aur INDEX hai, ek aur useful constraint hai:

CHECK

Example:

CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) CHECK (price >= 0)
);

Yahan price negative nahi hona chahiye.

Example:

INSERT INTO products (product_name, price)
VALUES ('Laptop', -500);

Database check constraint ke according is value ko reject kar sakta hai.

---

36. Constraints vs Indexes

Ye difference beginner ke liye bahut important hai.

Feature	Constraint	Index
Main purpose	Data rules enforce karna	Data access/search ko efficient banana
Example	PRIMARY KEY	INDEX
Example	UNIQUE	Composite INDEX
Example	NOT NULL	Index on email
Data validation	Yes, depending on constraint	Normally no
Performance	Indirect role ho sakta hai	Main purpose performance/access support

Simple way:

Constraint = Data ke rules

Index = Data ko efficiently access karne ki structure

---

37. PRIMARY KEY vs INDEX

Ye bhi commonly confusing topic hai.

PRIMARY KEY

id INT PRIMARY KEY

Purpose:

Row ko uniquely identify karna.

INDEX

CREATE INDEX idx_name
ON users(name);

Purpose:

Query ko efficiently support karna.

Primary key automatically indexed hoti hai, but har index primary key nahi hota.

---

38. UNIQUE vs PRIMARY KEY

Example:

id       → PRIMARY KEY
email    → UNIQUE
username → UNIQUE

Is design ka meaning:

• id → record identity
• email → unique email
• username → unique username

Ek table mein multiple unique constraints ho sakte hain, while primary key role unique row identification ke liye hota hai.

---

39. Too Many Indexes Kyu Problem Ho Sakte Hain?

Index useful hai, lekin har column par index banana good practice nahi hai.

Suppose table mein:

1,000,000 rows

hain aur aapne 15 unnecessary indexes bana diye.

Problems:

• Storage usage increase ho sakta hai.
• INSERT operations par additional index maintenance cost hoti hai.
• UPDATE operations par cost badh sakti hai.
• DELETE operations par bhi index maintenance hoti hai.
• Database design unnecessarily complex ho sakta hai.

Isliye:

Only create indexes that support real query patterns and constraints.

---

40. Kin Columns Par Index Consider Karna Chahiye?

Common candidates:

• Frequently searched columns
• Frequently filtered columns
• Join columns
• Frequently sorted/grouped columns, where appropriate
• Columns involved in important application queries

Examples:

WHERE email = ?

WHERE user_id = ?

JOIN orders ON users.id = orders.user_id

WHERE status = ?

Lekin final decision actual workload aur EXPLAIN analysis ke basis par lena better hota hai.

---

41. EXPLAIN Se Query Analyze Karna

MySQL mein query execution plan samajhne ke liye:

EXPLAIN
SELECT *
FROM users
WHERE email = 'rahul@gmail.com';

EXPLAIN se aap dekh sakte ho ki MySQL query ko execute karne ke liye kis access strategy/index ko choose kar raha hai.

Index optimization ke time EXPLAIN bahut useful tool hai.

---

42. Real-World Login Example

Suppose login query:

SELECT id, username, password
FROM users
WHERE email = 'rahul@gmail.com';

Agar email unique hai:

email VARCHAR(150) UNIQUE

to uniqueness ke saath associated index lookup ko support karega.

Isliye email ko separately normal index karna generally unnecessary hoga.

---

43. Real-World Search Example

Suppose blog website hai.

Table:

CREATE TABLE posts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(200) NOT NULL,
    slug VARCHAR(220) NOT NULL UNIQUE,
    status VARCHAR(20) DEFAULT 'published'
);

Yahan:

id → PRIMARY KEY
slug → UNIQUE
status → DEFAULT
title → NOT NULL

Agar application frequently status ke according posts fetch karti hai:

SELECT *
FROM posts
WHERE status = 'published';

To workload ke basis par status par index consider kiya ja sakta hai:

CREATE INDEX idx_posts_status
ON posts(status);

---

44. Common Beginner Mistakes

Mistake 1: Har Column Par INDEX Banana

Galat approach:

CREATE INDEX idx1 ON users(name);
CREATE INDEX idx2 ON users(email);
CREATE INDEX idx3 ON users(status);
CREATE INDEX idx4 ON users(created_at);
CREATE INDEX idx5 ON users(phone);

Sirf isliye ki index fast hota hai.

Better approach:

Actual queries ko analyze karke indexes create karein.

---

Mistake 2: PRIMARY KEY Aur INDEX Ko Same Samajhna

PRIMARY KEY:

Data identity + uniqueness

INDEX:

Efficient data access

Dono related hain, but same concept nahi hain.

---

Mistake 3: UNIQUE Aur NOT NULL Ka Difference Na Samajhna

email VARCHAR(150) UNIQUE

duplicate values prevent karta hai.

email VARCHAR(150) NOT NULL

NULL prevent karta hai.

Agar email required bhi hai aur unique bhi:

email VARCHAR(150) NOT NULL UNIQUE

use kar sakte hain.

---

Mistake 4: DEFAULT Ko Data Validation Samajhna

DEFAULT ka purpose hai:

Value missing hone par fallback value provide karna.

Ye automatically har invalid value ko reject nahi karta.

---

Mistake 5: Same Column Par Duplicate Index Banana

Agar:

email VARCHAR(150) UNIQUE

already hai, to same email column par unnecessary normal index create karne se avoid karein.

---

Mistake 6: Composite Index Ka Order Ignore Karna

Example:

CREATE INDEX idx_customer_status
ON orders(customer_id, status);

Column order query performance ke liye important ho sakta hai.

Index design query patterns ko dekhkar karein.

---

45. Best Practices

1. Har Main Table Mein Proper Primary Key Rakhein

Example:

id INT PRIMARY KEY AUTO_INCREMENT

2. Business-Unique Values Ko UNIQUE Rakhein

Examples:

email
username
SKU
slug

3. Required Fields Par NOT NULL Use Karein

Example:

name VARCHAR(100) NOT NULL

4. Sensible Defaults Use Karein

Example:

status VARCHAR(20) DEFAULT 'active'

5. Query Patterns Ke According Index Design Karein

Sirf guess karke indexes na banayein.

6. Existing Indexes Check Karein

SHOW INDEX FROM users;

7. Query Performance Ke Liye EXPLAIN Use Karein

EXPLAIN SELECT ...

8. Duplicate/Redundant Indexes Avoid Karein

Agar existing index already required query ko support karta hai, unnecessary second index na banayein.

---

46. Complete Practical Database Example

Ab ek small e-commerce database structure banate hain.

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(150) NOT NULL UNIQUE,
    status VARCHAR(20) NOT NULL DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Products:

CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(150) NOT NULL,
    sku VARCHAR(50) NOT NULL UNIQUE,
    price DECIMAL(10,2) NOT NULL,
    stock INT NOT NULL DEFAULT 0,
    status VARCHAR(20) NOT NULL DEFAULT 'active'
);

Orders:

CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'pending',
    total_amount DECIMAL(10,2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

Possible index:

CREATE INDEX idx_orders_user_status
ON orders(user_id, status);

Ab database mein:

• Unique users
• Unique emails
• Unique product SKUs
• Required fields
• Default statuses
• Primary keys
• Foreign keys
• Query-supporting indexes

sab properly design kiye ja sakte hain.

---

47. Quick Reference

Requirement	Use
Record ko uniquely identify karna	PRIMARY KEY
Duplicate value prevent karna	UNIQUE
NULL prevent karna	NOT NULL
Missing value par automatic value	DEFAULT
Query/data lookup ko efficiently support karna	INDEX
Multiple columns par index	Composite INDEX
Existing index remove karna	DROP INDEX
Index check karna	SHOW INDEX
Query plan check karna	EXPLAIN

---

48. Important SQL Syntax Cheat Sheet

PRIMARY KEY

id INT PRIMARY KEY

AUTO_INCREMENT PRIMARY KEY

id INT PRIMARY KEY AUTO_INCREMENT

UNIQUE

email VARCHAR(150) UNIQUE

NOT NULL

name VARCHAR(100) NOT NULL

DEFAULT

status VARCHAR(20) DEFAULT 'active'

INDEX

CREATE INDEX idx_name
ON users(name);

UNIQUE INDEX

CREATE UNIQUE INDEX idx_email
ON users(email);

Composite INDEX

CREATE INDEX idx_status_name
ON users(status, name);

DROP INDEX

DROP INDEX idx_name
ON users;

SHOW INDEX

SHOW INDEX FROM users;

EXPLAIN

EXPLAIN
SELECT *
FROM users
WHERE email = 'rahul@gmail.com';

---

49. Practice Exercises

Ab khud practice karne ke liye ye exercises try karein.

Exercise 1

students table create karein:

id
name
email
age
status

Requirements:

• id → PRIMARY KEY + AUTO_INCREMENT
• name → NOT NULL
• email → UNIQUE + NOT NULL
• status → DEFAULT 'active'

---

Exercise 2

products table banayein jisme:

product_id
product_name
sku
price
stock

Requirements:

• product_id → PRIMARY KEY
• product_name → NOT NULL
• sku → UNIQUE
• price → NOT NULL
• stock → DEFAULT 0

---

Exercise 3

Products ke product_name par index create karein.

CREATE INDEX ...

---

Exercise 4

Orders table banayein jisme:

order_id
customer_id
status
total_amount

Aur customer_id + status par composite index create karein.

---

Exercise 5

Check karein ki users table mein kaun-kaun se indexes available hain.

Hint:

SHOW INDEX FROM users;

---

Exercise 6

Kisi indexed column par query run karke:

EXPLAIN

ke through execution plan observe karein.

---

50. Easy Way To Remember

MySQL Constraints ko yaad rakhne ka simple formula:

PRIMARY KEY
      ↓
Who is this row?

UNIQUE
      ↓
Duplicate allowed nahi

NOT NULL
      ↓
Value required

DEFAULT
      ↓
Value nahi di → automatic value

INDEX
      ↓
Data ko efficiently find/access karne mein help

Ek real-world user table ko imagine karein:

id
 ↓
PRIMARY KEY

email
 ↓
UNIQUE

name
 ↓
NOT NULL

status
 ↓
DEFAULT 'active'

frequently searched columns
 ↓
INDEX

---

Conclusion

MySQL mein database ko reliable aur efficient banane ke liye Constraints aur Indexes bahut important hain.

Aaj humne seekha:

• PRIMARY KEY record ko uniquely identify karti hai.
• UNIQUE duplicate values ko prevent karta hai.
• NOT NULL required fields ke liye use hota hai.
• DEFAULT missing value ke liye automatic value provide karta hai.
• INDEX data access/search ko efficiently support karta hai.
• COMPOSITE INDEX multiple columns ko ek index mein combine karta hai.
• SHOW INDEX se indexes check kar sakte hain.
• EXPLAIN se query execution plan analyze kar sakte hain.
• Har column par index banana good practice nahi hai.
• Proper constraints database ki data integrity maintain karne mein help karte hain.

Agar aap PHP, CodeIgniter 4, Laravel ya kisi bhi backend application ke saath MySQL use kar rahe hain, to ye concepts real-world database design ke liye extremely important hain.