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_id | dept_name |
|---|
| 1 | HR |
| 2 | Finance |
| 3 | IT |
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_id | emp_name | dept_id |
|---|
| 1 | Alice | 1 |
| 2 | Bob | 2 |
| 3 | Charlie | 3 |
| 4 | David | 1 |
| 5 | Eve | 2 |
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_id | emp_name | dept_id |
|---|
| 2 | Bob | 2 |
| 3 | Charlie | 3 |
| 5 | Eve | 2 |
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:
| dept_id | dept_name |
|---|
| 1 | HR |
| 2 | Finance |
| 3 | IT |
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.
Similar Reads
PL/SQL DELETE JOIN
In database management, removing specific records from a table is a common task especially when cleaning or updating data. One useful technique is the DELETE JOIN which allows us to delete records from one table based on conditions involving another table. In this article, we will explore PL/SQL DEL
4 min read
Cascade in SQL
Structured Query Language (SQL) is the backbone of relational database management systems, Enabling developers to manipulate and manage data effectively. One crucial feature for maintaining data integrity in SQL is the CASCADE operation, which simplifies handling relationships between parent and chi
4 min read
SQL DELETE JOIN
The SQL DELETE JOIN statement is a powerful feature that allows us to delete rows from one table based on conditions involving another table. This is particularly useful when managing relationships between tables in a database. For example, we may want to delete rows in a "Library Books" table where
4 min read
SQLAlchemy Cascading Deletes
SQLAlchemy is a powerful ORM (Object-Relational Mapping) library for Python that allows developers to interact with relational databases using Python objects. It provides a rich set of tools for querying, updating, and manipulating data, making it a popular choice for building web applications and o
4 min read
Python : __delete__ vs __del__
Both __delete__ and __del__ are dunder or magic methods in Python. Dunder or magic methods in Python are the methods having two prefix and suffix underscores in the method name. Dunder here means “Double Under (Underscores)â€. These are commonly used for operator overloading. __del__ __del__ is a des
2 min read
MySQL DELETE JOIN
MySQL is an open-source, user-friendly, powerful, and popular choice, relational database management system. When maintaining and modifying data, tables usually interact in a complex way. MySQL's DELETE JOIN function is one of its most powerful functions. MySQL DELETE JOIN is explored in detail in t
4 min read
How to Delete Column in SQL
In SQL, deleting a column from an existing table is a straightforward process, but it's important to understand the implications and the correct syntax involved. While there is no direct DELETE COLUMN command in SQL, we can achieve this by using the ALTER TABLE command combined with DROP COLUMN. In
5 min read
When to Use ON UPDATE CASCADE in PL/SQL?
In Oracle PL/SQL, managing the update of related records in child tables can be challenging, especially when dealing with complex data relationships. The ON UPDATE CASCADE option in Oracle provides a powerful solution to automate the update of child records whenever the corresponding parent record i
4 min read
PL/SQL DELETE Statement
In PL/SQL(Procedural Language/Structured Query Language), the DELETE statement is the powerful command used to remove one or more records from the database table. It is an essential part of database management and enables the users to efficiently manage and maintain the data integrity by selectively
7 min read
Difference Between DELETE and DROP in SQL
In SQL, the DELETE and DROP commands are essential for managing data in a database, but they serve different purposes. While both are used to remove data, their functionality varies significantly. The DELETE command is designed to remove specific rows (tuples) or all rows from a table while preservi
4 min read