Delete Dependent Data with DELETE CASCADE

Managing dependent data across multiple tables can be challenging, especially as databases grow in size and complexity. The DELETE CASCADE statement provides a powerful solution by automatically removing associated records in child tables when a parent record is deleted.

What You’ll Learn

By the end of this lesson, you will understand:

• How DELETE CASCADE works in a relational database.
• How to define a foreign key with a DELETE CASCADE condition.
• The benefits of using DELETE CASCADE for database maintenance.

Understanding DELETE CASCADE

The DELETE CASCADE statement allows you to delete rows in a parent table and automatically remove any associated rows in child tables. This is done by defining a foreign key constraint with the ON DELETE CASCADE condition.

Syntax

CREATE TABLE  (
   id INT NOT NULL PRIMARY KEY
);

CREATE TABLE  (
   id INT NOT NULL PRIMARY KEY,
    INT FOREIGN KEY REFERENCES (id) ON DELETE CASCADE
);
    

In this example:

• <table_1>: The parent table, containing a primary key column.
• <table_2>: The child table, containing a foreign key that references the parent table’s primary key.
• ON DELETE CASCADE: Ensures that when a record in the parent table is deleted, all matching records in the child table are also deleted.

DELETE CASCADE Example

Let’s consider an example with employees and their performance reviews:

Schema

employees table:

CREATE TABLE employees (
   id INT NOT NULL PRIMARY KEY,
   name TEXT NOT NULL,
   department TEXT NOT NULL
);
    

reviews table:

CREATE TABLE reviews (
   id INT NOT NULL PRIMARY KEY,
   employee_id INT FOREIGN KEY REFERENCES employees(id) ON DELETE CASCADE,
   review TEXT NOT NULL
);
    

In this schema:

• The reviews table’s employee_id column is a foreign key referencing the id column in the employees table.
• Using ON DELETE CASCADE, any review associated with a deleted employee will also be removed automatically.

Sample Data

employees table:

id   name    department
1    Cooper  Accounting
2    Indie   HR
3    Kota    Project Manager
    

reviews table:

id   employee_id   review
1    3              2020
2    1              2021
3    2              2021
4    3              2021
    

DELETE CASCADE in Action

When you delete a record from the employees table, the corresponding records in the reviews table are automatically removed:

DELETE FROM employees WHERE name = 'Kota';
    

This action triggers:

DELETE FROM reviews WHERE employee_id = 3;
    

Updated Tables

employees table:

id   name    department
1    Cooper  Accounting
2    Indie   HR
    

reviews table:

id   employee_id   review
2    1              2021
3    2              2021
    

Benefits of DELETE CASCADE

Using DELETE CASCADE simplifies database maintenance by:

• Automatically removing dependent data, ensuring consistency and integrity.
• Eliminating the need to manually track and delete child records.
• Preventing orphaned records in child tables, keeping the database organized.

SQL Commands Recap

To use DELETE CASCADE, ensure that foreign keys are enabled in SQLite3:

PRAGMA foreign_keys = ON;
    

Then, define your schema with ON DELETE CASCADE:

CREATE TABLE parent_table (
   id INT NOT NULL PRIMARY KEY
);

CREATE TABLE child_table (
   id INT NOT NULL PRIMARY KEY,
   parent_id INT FOREIGN KEY REFERENCES parent_table(id) ON DELETE CASCADE
);
    

What You Learned

In this lesson, you learned:

• How to define and use DELETE CASCADE in relational databases.
• The role of foreign keys in automatically maintaining data integrity.
• The benefits of using DELETE CASCADE for efficient database management.

Real-World Applications

DELETE CASCADE is widely used in:

• HR systems: Automatically removing an employee's records (e.g., reviews, project assignments) when they leave the organization.
• E-commerce platforms: Deleting orders and associated details when a customer account is removed.
• Content management systems: Removing all comments associated with a deleted post.

By using DELETE CASCADE, you can streamline database operations and ensure data consistency across related tables.