Oracle Constraints (The Guardrails) ⚖️

Mentor's Note: Without constraints, your database is just a messy notepad. Constraints are the rules that keep your data high-quality. They prevent people from entering negative ages or duplicate IDs. 💡

---

🌟 The Scenario: The School Registry 🏫

• Primary Key: Every student must have a unique Roll Number.
• Not Null: Every student must have a Name.
• Check: A student's age must be between 5 and 18.
• Foreign Key: A student can only be assigned to a Class ID that actually exists.

---

💻 1. Core Syntax

CREATE TABLE students (
    student_id NUMBER PRIMARY KEY,
    name       VARCHAR2(50) NOT NULL,
    email      VARCHAR2(100) UNIQUE,
    age        NUMBER CHECK (age >= 5),
    city       VARCHAR2(50) DEFAULT 'Surat'
);

---

💻 2. Foreign Keys & Referential Integrity

Links two tables together.

ALTER TABLE enrollments 
ADD CONSTRAINT fk_student 
FOREIGN KEY (student_id) REFERENCES students(student_id);

---

🏗️ Architect's Note: Performance & Constraints 🛡️

Constraints aren't just for data quality; they help the Oracle Optimizer.

• The Architect's Secret: When Oracle knows a column is NOT NULL, it can skip certain internal checks, making your queries faster.
• The Index Trick: Oracle automatically creates an Index for Primary Keys and Unique Keys.
• Architect's Tip: Foreign Keys DO NOT get an automatic index. You should almost always create one manually on Foreign Key columns to prevent performance slowdowns during Joins!

---

📊 Summary Table

Constraint	Can be NULL?	Unique?	Auto-Indexed?
Primary Key	❌ No	✅ Yes	✅ Yes
Unique	✅ Yes	✅ Yes	✅ Yes
Not Null	❌ No	--	❌ No

---

📈 Learning Path

• ← Back: Data Types
• Next: Sequences →