Oracle Constraints (The Guardrails) ⚖️¶

Prerequisites: Table Management

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