Q.7

Integrity Constraints Kya Hain?

Basic Concept:
Integrity constraints wo rules (niyam) hote hain jo ek database par lagaye jaate hain taaki data ki correctness (sahi hona), validity (manyata), aur consistency (ek jaisa rehna) ensure ki ja sake. Yeh constraints system mein galat (invalid) data ko enter hone se rokte hain aur tables ke beech ke logical relationships ko maintain karte hain.

Major types ke integrity constraints yeh hain: Domain Constraints, Entity Integrity Constraints, Key Constraints, Referential Integrity Constraints, Assertions, aur Triggers.

---

1. Domain Constraints (Column ke Rules)

• Explanation: Domain constraints yeh tay karte hain ki ek attribute (column) mein kis tarah ki values store ki ja sakti hain. Yeh ensure karte hain ki jo data enter ho raha hai wo ek valid aur pehle se define kiye gaye 'domain' ka hissa ho (jaise data integer hoga, character hoga, date hoga, ya kisi specific range ka hoga).
• Isme NOT NULL, CHECK conditions, valid formats aur allowable ranges jaise rules aate hain.

Example: Ek Student table mein:

• Semester ki value sirf 5th, 6th, 7th ho sakti hai.
• Age sirf 18 se 30 ke beech ka number (integer) hona chahiye.
• Name mein sirf alphabets hone chahiye.
• Agar Semester ko 'NOT NULL' banaya gaya hai, toh aap isme NULL (khali) value nahi daal sakte.

2. Entity Integrity Constraint (Row ki Pehchan)

• Explanation: Entity integrity ka rule yeh kehta hai ki kisi bhi relation (table) ki Primary Key ki value hamesha unique honi chahiye aur wo kabhi NULL (khali) nahi ho sakti.
• Kyunki primary key table ki har ek row (tuple) ko uniquely identify karti hai (alag pehchan deti hai), agar hum usme NULL ya duplicate value dalenge, toh records ko ek dusre se alag pehchanna namumkin ho jayega.

Example: Agar Student table mein Student_id primary key hai, toh aisi row jisme Student_id = NULL ho, wo is rule ko todti hai (violate karti hai) kyunki us record ki koi pehchan nahi rahegi.

3. Key Constraints (Uniqueness)

• Explanation: Key constraints yeh ensure karte hain ki table mein jo attributes 'keys' ke roop mein chune gaye hain, wo har tuple ko uniquely identify karein.
• Ek key mein duplicate values nahi ho sakti. Agar aap koi aisi row insert karne ki koshish karenge jisme pehle se maujud (duplicate) key value ho, toh database use reject kar dega. Isme Primary Key aur Unique Key shamil hote hain.

Example: Agar Student_id ek key hai, toh do students ki ID 21CSE102 nahi ho sakti. Sabki ID alag honi chahiye.

4. Primary Key Constraint (Sabse Zaroori Key)

• Explanation: Yeh constraint simply yeh ensure karta hai ki jo attribute Primary Key chuna gaya hai, wo Unique bhi ho aur NOT NULL bhi ho.
• Koi bhi do rows ka primary key same nahi ho sakta, aur is field ko khali bhi nahi chhoda ja sakta. Yeh duplications ko avoid karne ke liye hota hai.

Example: Agar Student_id = 101 pehle se table mein hai, toh ek aur row mein 101 daalna reject ho jayega. Wahi agar aap Student_id = NULL daalenge toh wo bhi reject ho jayega.

5. Unique Key Constraint (Sab Alag Hona Chahiye)

• Explanation: UNIQUE key constraint yeh ensure karta hai ki ek column ki saari values ek dusre se alag (distinct) hon. Yeh tab use hota hai jab aapko values toh alag chahiye, par wo table ki main pehchan (primary key) nahi hoti.
• Important Note (Exams ke liye): UNIQUE key mein NULL values aa sakti hain. Kitne NULL allowed hain yeh RDBMS (jaise Oracle, MySQL) par depend karta hai. Lekin university exams ke liye hamesha likhein: "UNIQUE allows NULL values."

Example: Ek Employee table mein Email column ko UNIQUE banaya ja sakta hai taaki sabka email alag ho. (aniket@... aur shashwat@... allowed hain). Exam theory ke hisaab se isme sirf ek NULL allow hota hai.

6. Referential Integrity Constraints (Do Tables ka Rishta)

• Explanation: Referential integrity do tables ke beech ke relationships ki validity ko banaye rakhta hai. Yeh ensure karta hai ki ek table ki Foreign Key hamesha dusri table ki Primary Key mein maujud kisi value ko hi refer kare.
• Agar referenced value (dusri table mein) nahi hai, toh insertion reject ho jata hai. Yeh "orphan records" (aise records jinka koi connection na ho) ko banne se rokat hai.

Example: Agar Student table mein Block_No ek foreign key hai jo Hostel/Block table ko refer karti hai, toh aap kisi student ko Block_No = 22 assign nahi kar sakte agar Block table mein 22 number ka koi block bana hi na ho.

7. Assertion (Global Rules)

• Explanation: Assertion kisi ek table par lagne wala rule nahi hai, balki yeh ek global integrity rule hai jo poore database par lagta hai.
• Yeh aisi conditions se banta hai jo hamesha sach (true) rehni chahiye. Jab bhi database mein koi change hota hai, DBMS check karta hai ki assertion toot toh nahi raha. Yeh tab use hota hai jab rules thode complex hote hain aur simple table constraints se handle nahi hote.

Example: Ek rule (assertion) ho sakta hai ki "Ek bank branch dwara diya gaya total loan amount, us branch ke total account balance se hamesha kam hona chahiye". Agar koi update is rule ko todega, toh wo reject ho jayega.

8. Triggers (Automatic Actions)

• Explanation: Trigger ek procedural action (kuch code) hota hai jo database mein kisi event (jaise INSERT, UPDATE, ya DELETE) ke hone par automatically execute (chal) jata hai.
• Triggers ka use advanced integrity constraints lagane aur aise business rules implement karne ke liye hota hai jo normal constraints akele handle nahi kar sakte.

Example: Ek trigger banaya ja sakta hai jo check kare ki UPDATE hone ke baad account balance negative toh nahi ho gaya. Agar negative ho gaya, toh trigger automatically balance ko 0 kar dega aur bache hue amount ka ek "Loan entry" (overdraft) create kar dega.