Referential Integrity

🔗 Referential Integrity: The Family Tree of Your Database

The Big Idea

Imagine your database is a family photo album. Every photo (row) in the “Kids” album must point to a real person in the “Parents” album. You can’t have a picture of a kid saying “My parent is Bob” if Bob doesn’t exist!

Referential Integrity is the rule that keeps your database family tree honest. It makes sure every “child” record always points to a real “parent” record.

🌳 Referential Integrity Basics

What Is It?

Think of two tables connected by a string. The parent table holds the main records. The child table has records that “belong” to a parent.

graph TD
    A["Parents Table"] -->|connects to| B["Children Table"]
    A -->|"id = 1 #40;Mom#41;"| C["parent_id = 1"]
    A -->|"id = 2 #40;Dad#41;"| D["parent_id = 2"]

The Golden Rule

Every value in a foreign key column must match a value in the primary key column of the parent table—or be NULL.

Example:

-- Parent table
CREATE TABLE departments (
  id INT PRIMARY KEY,
  name VARCHAR(50)
);

-- Child table with foreign key
CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  dept_id INT,
  FOREIGN KEY (dept_id)
    REFERENCES departments(id)
);

Now employees.dept_id MUST match a real departments.id. No fake departments allowed!

Why Does This Matter?

Without referential integrity:

• 🚫 Employees could belong to departments that don’t exist
• 🚫 Orders could reference customers who were deleted
• 🚫 Your data becomes a tangled mess

With referential integrity:

• ✅ Data stays connected and meaningful
• ✅ No orphan records floating around
• ✅ Queries always find what they’re looking for

🌊 CASCADE Actions

The Domino Effect

Imagine you have a chain of dominoes. When the first one falls, all the others follow. CASCADE works the same way!

When you delete or update a parent record, all connected child records automatically get deleted or updated too.

CASCADE DELETE

When a department closes, all its employees are removed:

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  dept_id INT,
  FOREIGN KEY (dept_id)
    REFERENCES departments(id)
    ON DELETE CASCADE
);

What happens:

graph TD
    A["DELETE department id=5"] --> B["Automatically deletes<br>ALL employees with dept_id=5"]

Real Example:

-- Before: Department 5 has 3 employees
DELETE FROM departments
WHERE id = 5;
-- After: Department 5 AND all 3 employees = GONE

CASCADE UPDATE

When a department ID changes, all employee references update automatically:

FOREIGN KEY (dept_id)
  REFERENCES departments(id)
  ON UPDATE CASCADE

What happens:

-- Department changes from id=5 to id=50
UPDATE departments SET id = 50 WHERE id = 5;
-- All employees with dept_id=5 now have dept_id=50

⚠️ When to Use CASCADE

• ✅ Blog posts → Comments (delete post = delete comments)
• ✅ Users → User settings (delete user = delete settings)
• ⚠️ Be careful with important data!

🔄 SET NULL and SET DEFAULT Actions

SET NULL: The Gentle Goodbye

Instead of deleting child records, just erase their connection to the parent. Like removing a name tag but keeping the person.

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  dept_id INT,
  FOREIGN KEY (dept_id)
    REFERENCES departments(id)
    ON DELETE SET NULL
);

What happens:

graph TD
    A["DELETE department id=5"] --> B["Employees with dept_id=5<br>now have dept_id=NULL"]
    B --> C["Employees still exist!<br>Just no department assigned"]

Real Example:

-- Before: Employee Alice has dept_id = 5
DELETE FROM departments WHERE id = 5;
-- After: Alice still exists, but dept_id = NULL
-- (She's unassigned, waiting for a new department)

SET DEFAULT: The Safety Net

When the parent disappears, child records get a default value instead of NULL.

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  dept_id INT DEFAULT 1,
  FOREIGN KEY (dept_id)
    REFERENCES departments(id)
    ON DELETE SET DEFAULT
);

What happens:

-- Department 5 is deleted
-- All employees with dept_id=5 now have dept_id=1
-- (Assuming department 1 is "Unassigned" or "General")

When to Use Each

Examples:

• 📦 Products → Category: SET NULL (product exists, just uncategorized)
• 👤 Employees → Manager: SET DEFAULT (assign to “No Manager” record)

🚧 RESTRICT and NO ACTION

RESTRICT: The Strict Bodyguard

RESTRICT is like a bouncer who says “NO WAY!”

If you try to delete or update a parent that has children, the database blocks you immediately.

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  dept_id INT,
  FOREIGN KEY (dept_id)
    REFERENCES departments(id)
    ON DELETE RESTRICT
);

What happens:

graph TD
    A["Try: DELETE department id=5"] --> B{Has employees?}
    B -->|YES| C["❌ ERROR!<br>Cannot delete"]
    B -->|NO| D["✅ OK to delete"]

Real Example:

-- Department 5 has 3 employees
DELETE FROM departments WHERE id = 5;
-- ERROR: Cannot delete - employees still reference this department!

NO ACTION: The Delayed Checker

NO ACTION sounds like it does nothing, but it checks at the end of the statement instead of immediately.

In most databases, NO ACTION and RESTRICT behave the same way. The difference is when they check:

Why does timing matter?

In complex operations with triggers, NO ACTION lets other changes happen first. RESTRICT stops everything immediately.

FOREIGN KEY (dept_id)
  REFERENCES departments(id)
  ON DELETE NO ACTION

The Safe Choice

RESTRICT and NO ACTION are the safest options. They prevent accidents:

• ✅ Can’t accidentally delete a department with employees
• ✅ Can’t update a key that breaks connections
• ✅ Forces you to handle children first

When to Use

• 📊 Financial records (never lose transaction history)
• 👥 User accounts with important data
• 📝 Any data you CAN’T afford to lose by accident

🎯 Quick Summary

🏠 The House Analogy

Think of your database like houses on a street:

• CASCADE: Demolish house #5 → All furniture inside gets demolished too
• SET NULL: Owner moves away → House still exists, just marked “no owner”
• SET DEFAULT: Owner moves away → House assigned to “City Property Office”
• RESTRICT: Can’t demolish house if someone still lives there!

💡 Pro Tips

1. Start with RESTRICT - It’s the safest default
2. Use CASCADE carefully - It can delete more than you expect!
3. SET NULL needs nullable columns - Make sure your FK column allows NULL
4. SET DEFAULT needs a valid default - The default value must exist in parent table

🎉 You Did It!

You now understand how databases keep their family trees clean and connected. Referential integrity is like a caring parent—it makes sure no record gets lost or orphaned!

Remember: Good data relationships = Happy database = Happy you! 🌟