DEV Community

Pranav Bakare
Pranav Bakare

Posted on

2 1 1 1 1

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

Enter fullscreen mode Exit fullscreen mode

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
);
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

4. Delete from Parent Table:

DELETE FROM departments WHERE dept_id = 1;
Enter fullscreen mode Exit fullscreen mode

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

Tiugo image

Fast, Lean, and Fully Extensible

CKEditor 5 is built for developers who value flexibility and speed. Pick the features that matter, drop the ones that don’t and enjoy a high-performance WYSIWYG that fits into your workflow

Start now

Top comments (0)

Image of Datadog

Keep your GPUs in check

This cheatsheet shows how to use Datadog’s NVIDIA DCGM and Triton integrations to track GPU health, resource usage, and model performance—helping you optimize AI workloads and avoid hardware bottlenecks.

Get the Cheatsheet

👋 Kindness is contagious

Engage with a wealth of insights in this thoughtful article, valued within the supportive DEV Community. Coders of every background are welcome to join in and add to our collective wisdom.

A sincere "thank you" often brightens someone’s day. Share your gratitude in the comments below!

On DEV, the act of sharing knowledge eases our journey and fortifies our community ties. Found value in this? A quick thank you to the author can make a significant impact.

Okay