Open In App

PL/SQL ON DELETE CASCADE

Last Updated : 11 Sep, 2024
Comments
Improve
Suggest changes
Like Article
Like
Report

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:

dept_iddept_name
1HR
2Finance
3IT

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:

emp_idemp_namedept_id
1Alice1
2Bob2
3Charlie3
4David1
5Eve2

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:

emp_idemp_namedept_id
2Bob2
3Charlie3
5Eve2

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:

dept_iddept_name
3IT

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:

dept_iddept_name
1HR
2Finance
3IT

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.


Next Article
Article Tags :

Similar Reads