Lesson 09 � Intermediate

CONSTRAINTS:
DATA RULES.

Constraints se data ki quality maintain hoti hai � duplicate entries nahi, null values nahi, valid ranges hi. Database integrity ke liye zaroori hai. Bina constraints ke database mein garbage data bhar jayega.

WHY: Constraints kyun zaroori hain?

Socho ek school ka database hai � agar kisi student ka ID same ho jaye do students ka toh confusion ho jayega. Agar email field khali chhod di toh contact kaise karoge✓ Agar age negative daal di toh✓ Yeh sab problems constraints solve karte hain. They are the rules jo ensure karte hain ki data clean aur consistent rahe.

HOW: Constraints ka syntax samjho

Constraints CREATE TABLE ke andar define hote hain. Column ke saath likhte hain ya table level par. Formula simple hai � column_name TYPE CONSTRAINT.

-- PRIMARY KEY
CREATE TABLE students (
 id INT PRIMARY KEY,
 name VARCHAR(100) NOT NULL,
 email VARCHAR(100) UNIQUE,
 age INT CHECK (age >= 16)
);

-- FOREIGN KEY
CREATE TABLE enrollments (
 id INT PRIMARY KEY,
 student_id INT,
 course_id INT,
 FOREIGN KEY (student_id) REFERENCES students(id),
 FOREIGN KEY (course_id) REFERENCES courses(id)
);

-- DEFAULT value
CREATE TABLE products (
 id INT PRIMARY KEY,
 name VARCHAR(100) NOT NULL,
 price DECIMAL(10,2) DEFAULT 0.00,
 in_stock BOOLEAN DEFAULT TRUE
);

-- Composite primary key
CREATE TABLE order_items (
 order_id INT,
 product_id INT,
 quantity INT DEFAULT 1,
 PRIMARY KEY (order_id, product_id)
);

Constraints detailed mein

Composite Primary Key

Kabhi kabhi ek column unique nahi hota � do ya zyada columns mil ke unique identifier banate hain. Jaise order_items mein same order mein same product ek baar hi aa sakta hai:

-- Composite key: order + product combination unique
CREATE TABLE order_items (
 order_id INT,
 product_id INT,
 quantity INT DEFAULT 1,
 PRIMARY KEY (order_id, product_id)
);

-- Ab same order mein same product duplicate nahi hoga
INSERT INTO order_items VALUES (1, 101, 2);
INSERT INTO order_items VALUES (1, 101, 3); -- ERROR! Duplicate
INSERT INTO order_items VALUES (1, 102, 1); -- OK, different product

FOREIGN KEY relationships

Tables ke beech relationship banana FOREIGN KEY se hota hai. Students ko courses se link karo, orders ko customers se � yeh relational database ki power hai:

-- Parent table
CREATE TABLE courses (
 id INT PRIMARY KEY,
 course_name VARCHAR(100) NOT NULL,
 duration VARCHAR(50)
);

-- Child table with FOREIGN KEY
CREATE TABLE enrollments (
 id INT PRIMARY KEY,
 student_id INT,
 course_id INT,
 enrolled_date DATE DEFAULT CURRENT_DATE,
 FOREIGN KEY (student_id) REFERENCES students(id),
 FOREIGN KEY (course_id) REFERENCES courses(id)
);

-- Ab galat course_id daalenge toh error aayega
INSERT INTO enrollments VALUES (1, 1, 999); -- ERROR! course 999 exists nahi

Try it: Table banao

Neeche ka editor SQL jaisa hai. Yahan apni table likho aur "Run Query" dabao. Screen par results dikhenge � yeh browser-based simulation hai, real database nahi.

Common constraint mistakes

• PRIMARY KEY bhoolna: Har table mein PRIMARY KEY zaroori hai � bina ke rows ko uniquely identify nahi kar sakte. Auto-increment use karo id ke liye.
• NOT NULL ka glow: Important columns mein NOT NULL lagana mat bhoolna � name, email, phone jaise fields mein ye mandatory hai.
• CHECK constraint nahi lagana: Age, price, quantity jaise numerical fields mein CHECK lagao taaki invalid values na aayein. Age negative ya zero nahi honi chahiye.
• DEFAULT na dena: Jab column optional ho ya default value ho sake, toh DEFAULT lagao � status, created_at, is_active jaise fields mein.