SQL Constraints: The Data Rules ⚖️

Mentor's Note: Constraints are the "Guardrails" of your database. Without them, you could have a student with no name, or a negative salary. Constraints ensure your data stays high-quality and trustworthy. 💡

---

🌟 The Scenario: The School Registry 🏫

Imagine you are the principal of a school.

1. NOT NULL: Every student must have a name. 👤
2. UNIQUE: No two students can have the same Email ID. 📧
3. PRIMARY KEY: Every student must have a unique Roll Number to identify them. 🆔
4. CHECK: A student's age must be at least 5 years old. 🎂
5. DEFAULT: If no city is provided, assume they are from "Surat". 📍

---

🎨 Visual Logic: The Constraint Guard

---

💻 Common Constraints (DDL Syntax)

Oracle SQL

CREATE TABLE STUDENTS (
    roll_no   NUMBER(5) PRIMARY KEY, -- 🆔 Unique ID
    name      VARCHAR2(50) NOT NULL, -- 👤 Mandatory
    email     VARCHAR2(100) UNIQUE,  -- 📧 No Duplicates
    age       NUMBER(3) CHECK (age >= 5), -- 🎂 Age Rule
    city      VARCHAR2(50) DEFAULT 'Surat' -- 📍 Auto-fill
);

MySQL

CREATE TABLE STUDENTS (
    roll_no   INT PRIMARY KEY AUTO_INCREMENT,
    name      VARCHAR2(50) NOT NULL,
    email     VARCHAR2(100) UNIQUE,
    age       INT CHECK (age >= 5),
    city      VARCHAR2(50) DEFAULT 'Surat'
);

---

📖 Deep Dive: Primary vs Foreign Key

1. Primary Key (The Anchor ⚓)

• Unique identify for every row.
• Cannot be NULL.
• Only one per table.

2. Foreign Key (The Bridge 🌉)

• Links one table to another.
• Points to the Primary Key of another table.
• Maintains Referential Integrity.

-- 🌉 Linking Employees to Departments
CREATE TABLE EMPLOYEE (
    emp_id NUMBER PRIMARY KEY,
    dept_id NUMBER REFERENCES DEPARTMENT(dept_id) -- 🔗 Foreign Key
);

---

📊 Comparison Table

Constraint	Can be NULL?	Unique?	Limit per Table
PRIMARY KEY	❌ No	✅ Yes	Only 1
UNIQUE	✅ Yes	✅ Yes	Multiple
NOT NULL	❌ No	❌ No	Multiple

---

💡 Interview Tip 👔

"Interviewers often ask: 'Can a UNIQUE constraint have a NULL value?' The answer is YES! In most databases, you can have one (or sometimes multiple) NULL values in a UNIQUE column, because NULL is not equal to NULL."

---

📈 Learning Path

• ← Back: Tables Hub
• Next: SQL Joins →

---

💡 Pro Tip: "Bad data is worse than no data. Use constraints early so you don't have to clean up a mess later."