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.

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

🎨 Visual Logic: The Constraint Guard¶

graph TD
    A[Data Entry 📥] --> B{Constraints?}
    B -- "Fail ❌" --> C[Reject Action 🚫]
    B -- "Pass ✅" --> D[Save to Table 🗄️]

    subgraph "Rule Set"
    B1[NOT NULL]
    B2[UNIQUE]
    B3[PRIMARY KEY]
    B4[CHECK]
    end

💻 Common Constraints (DDL Syntax)¶

Oracle SQLMySQL

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
);

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¶

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