PL/SQL ON DELETE CASCADE (original) (raw)

Last Updated : 23 Jul, 2025

The **ON DELETE CASCADE option in **PL/SQL is used to automatically delete rows from a child table when the corresponding row in the **parent table is deleted. This feature helps maintain referential integrity by ensuring that related records in child tables are removed in **sync with changes in the parent table.

This is particularly useful in relational databases where entities are **linked, and **deleting a parent record should also remove its dependent records. This article will explore the concept of ON DELETE CASCADE in PL/SQL, and explain examples with their **syntax, and output.

PL/SQL ON DELETE CASCADE

The ON DELETE CASCADE constraint is applied to **foreign key relationships to specify that when a row in the **parent table is deleted, all corresponding rows in the **child table should be automatically deleted. This ensures consistency and prevents **orphaned records.

**Syntax:

CREATE TABLE child_table (

child_id INT PRIMARY KEY,

parent_id INT,

FOREIGN KEY (parent_id) REFERENCES parent_table(parent_id) ON DELETE CASCADE

);

**Explanation:

This syntax defines a**child_table** with a primary key column (child_id) and a foreign key column (parent_id). The foreign key ensures **referential integrity by linking records in child_table to parent_table, and the **ON DELETE CASCADE**option ensures that any related child records are automatically removed when the corresponding parent record is deleted.

Examples of PL/SQL ON DELETE CASCADE

The **ON DELETE CASCADE clause is used in PL/SQL to automatically delete related records in a child table when the corresponding record in the parent table is deleted.

This ensures referential integrity by preventing **orphaned records in the child table.

Table 1: Departments

The departments table is created with two columns:dept_id, which is the primary key, and dept_name, which stores the **department names. The dept_id uniquely identifies each department.

The CREATE TABLE statement creates the departments table with dept_id as the primary key. The INSERT INTO statements add three records into the departments table, representing three departments: **HR, **Finance, and **IT.

**Query:

CREATE TABLE departments (
dept_id INT PRIMARY KEY,
dept_name VARCHAR(50)
);

INSERT INTO departments (dept_id, dept_name) VALUES (1, 'HR');
INSERT INTO departments (dept_id, dept_name) VALUES (2, 'Finance');
INSERT INTO departments (dept_id, dept_name) VALUES (3, 'IT');

**Output:

**Explanation:

• The output will be the **successful creation of the departments table and the insertion of three records into it, with dept_id values 1, 2, and 3 corresponding to the HR, Finance, and IT departments respectively.
• The output is **not directly visible in the database but can be confirmed by **querying the departments table.

Table 2: Employees

The employees table is created with a foreign key constraint on dept_id, which references the departments table.

The ON DELETE CASCADE clause ensures that if a **department is deleted from the departments table, all related records in the employees table are automatically deleted. The INSERT statements add five employees, each linked to a department by dept_id

Query:

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

INSERT INTO employees (emp_id, emp_name, dept_id) VALUES (1, 'Alice', 1);
INSERT INTO employees (emp_id, emp_name, dept_id) VALUES (2, 'Bob', 2);
INSERT INTO employees (emp_id, emp_name, dept_id) VALUES (3, 'Charlie', 3);
INSERT INTO employees (emp_id, emp_name, dept_id) VALUES (4, 'David', 1);
INSERT INTO employees (emp_id, emp_name, dept_id) VALUES (5, 'Eve', 2);

**Output:

**Explanation:

• The output displays a table of employees along with their corresponding department IDs. Each emp_id is **unique, showing the **employee's name (emp_name) and the department they belong to (dept_id).
• The data reflects the **successful insertion of employee records, with departments linked by **foreign key constraints.

Example 1: Delete Department and Automatically Remove Employees

**Deleting a department will **automatically trigger the deletion of all employees associated with that department due to the ON DELETE CASCADE clause in the **employees table.

In this case, employees "**Alice" and "**David," who were linked to dept_id = 1 (HR department), are also deleted. This action ensures referential integrity, preventing orphaned records in the employees table that would no longer have a **corresponding department.

**Query:

DELETE FROM departments WHERE dept_id = 1;

**Output:

**Explanation:

• When we delete the department with dept_id = 1 from the departments table, the **ON DELETE CASCADE**clause automatically **removes all employees in the employees table associated with that department.
• All **employees linked to the deleted department are **removed.
• The **department no longer exists in the departments table.

Example 2: Deleting Multiple Departments

When **deleting multiple departments, the **cascade delete feature ensures that all employees related to those departments are also removed.

In this query, deleting **departments with dept_id values 1 and 2 triggers the **ON DELETE CASCADE**action in the employees table. This operation ensures that the database remains **consistent and no employees are left without a valid department.

**Query:

DELETE FROM departments WHERE dept_id IN (1, 2);

**Output:

**Explanation:

• Employees associated with the deleted departments are **automatically removed.
• Specifically, employees "Alice," "David," "Bob," and "Eve" are deleted because their associated **departments (HR and Finance) were removed
• Only departments not targeted by the delete operation remain.
• Only the IT department and its associated employee "**Charlie" remain in the **respective tables.

Example 3: Attempt to Delete a Non-Existent Department

If we try to delete a department that doesn't exist, the **ON DELETE CASCADE clause has no effect because there are no related records to delete.

In this scenario, attempting to delete a department with dept_id = 4 results in no changes because this department does not exist in the departments table. The ON DELETE CASCADE clause has no effect since there are no related records in the employees table to delete.

**Query:

DELETE FROM departments WHERE dept_id = 4;

**Output:

**Explanation:

• No changes occur in the tables since the specified department does not exist.
• **Data integrity is maintained without any unintended deletion
• Consequently, both the departments and employees tables remain unchanged, preserving **data integrity without any **unintended deletions.
• This ensures that only existing records are targeted by delete operations.

Conclusion

The **ON DELETE CASCADE option is a valuable feature in SQL that maintains **referential integrity by ensuring that child records are automatically deleted when their parent record is removed. This option simplifies **data management and prevents orphaned records, making it easier to keep related data consistent.