ON DELETE CASCADE Usage with Foreign key

Definition of ON DELETE CASCADE

The ON DELETE CASCADE clause is used in a database when defining a foreign key constraint. It ensures that when a record in the parent table (with the primary key) is deleted, all related records in the child table (with the foreign key) are automatically deleted.

This prevents orphaned records in the child table and maintains data integrity across related tables.

Why is ON DELETE CASCADE Required?

• Maintain Referential Integrity: Ensures that no child record references a non-existent parent record.
• Simplifies Deletion Operations: Automatically handles deletion of related records, reducing the need for additional code or manual deletion.
• Avoids Orphan Records: Prevents dangling foreign key references in the child table when a parent record is removed.

Purpose of ON DELETE CASCADE

• Ensures data consistency by propagating deletions from parent to child tables.
• Simplifies database maintenance and reduces the risk of human error when deleting related records.
• Provides a way to enforce business rules for cascading deletions.

How It Works:

• The parent table has a primary key.
• The child table has a foreign key that references the primary key of the parent table.
• When a row in the parent table is deleted, rows in the child table that reference the deleted row are also deleted automatically.

Simple Example

1. Create Parent Table:

CREATE TABLE departments (
    dept_id NUMBER PRIMARY KEY,
    dept_name VARCHAR2(100)
);

2. Create Child Table:

CREATE TABLE employees (
    emp_id NUMBER PRIMARY KEY,
    emp_name VARCHAR2(100),
    dept_id NUMBER,
    CONSTRAINT fk_dept FOREIGN KEY (dept_id)
    REFERENCES departments (dept_id)
    ON DELETE CASCADE
);

Here, employees references departments with ON DELETE CASCADE.

3. Insert Data:

-- Insert data into departments
INSERT INTO departments (dept_id, dept_name) VALUES (1, 'HR');
INSERT INTO departments (dept_id, dept_name) VALUES (2, 'Finance');

-- Insert data into employees
INSERT INTO employees (emp_id, emp_name, dept_id) VALUES (101, 'John', 1);
INSERT INTO employees (emp_id, emp_name, dept_id) VALUES (102, 'Jane', 1);
INSERT INTO employees (emp_id, emp_name, dept_id) VALUES (103, 'Doe', 2);

4. Delete from Parent Table:

DELETE FROM departments WHERE dept_id = 1;

This will automatically delete all employees where dept_id = 1 (John and Jane) because of the **ON DELETE CASCADE** clause.

Analogy

• Imagine a family tree:
• Parent: Represents a department.
• Child: Represents employees working in the department.

If a parent (department) is removed from the tree, all its children (employees) are also removed automatically, because they cannot exist without the parent.

Key Benefits

• 1. Prevents orphan data: If a department is deleted, employees in that department won’t remain in the database without a valid department.
• 2. Reduces complexity: You don’t need to manually delete related rows in the child table.
• 3. Improves consistency: Guarantees that data in the database reflects valid relationships.

When Not to Use ON DELETE CASCADE

• When deletion of child records should be handled explicitly for audit or business purposes.
• In cases where child data must remain even if the parent record is deleted (e.g., historical logs).