When to Use ON UPDATE CASCADE in PL/SQL?
In PL/SQL, managing the update of related records in child tables can be a challenging task especially when dealing with complex data relationships. The āON UPDATE CASCADEā option provides a powerful solution to automate the update of child records when the corresponding parent record is updated. In this article, We will explore the scenarios where āON UPDATE CASCADEā is beneficial along with some examples and so on.
When to Use the āON UPDATE CASCADEā Clause?
When a record in a parent table is updated, it often requires updating related records in child tables to maintain data consistency. Manually updating these child records can be timeāconsuming and errorālikely, especially in large databases with complex relationships. We will understand through the below examples.
Letās set up an environment
The syntax for using āON UPDATE CASCADEā is applied when defining a foreign key constraint in the child table. Here is an example:
CREATE TABLE parent_table (
parent_id INT PRIMARY KEY
);
CREATE TABLE child_table (
child_id INT PRIMARY KEY,
parent_id INT,
FOREIGN KEY (parent_id) REFERENCES parent_table(parent_id) ON UPDATE CASCADE
);
Explanation: The above query creates two tables: parent_tathe le
with a primary key parent_id
, and child_table
with a primary key child_id
and a foreign key parent_id
referencing the parent_id
in parent_table
. The ON UPDATE CASCADE
clause ensures that if the parent_id
in parent_table
is updated, the corresponding parent_id
in child_table
is also updated to maintain referential integrity.
Example of āON UPDATE CASCADEā in PL/SQL
Example 1: Updating Parent Records and Automatically Updating Child Records
-- Sample Data
INSERT INTO parent_table VALUES (1);
INSERT INTO child_table VALUES (101, 1), (102, 1);
-- Update Parent Record
UPDATE parent_table SET parent_id = 2 WHERE parent_id = 1;
-- Display Updated Data
SELECT * FROM child_table;
Output:
child_id |
parent_id |
---|---|
101 |
2 |
102 |
2 |
Explanation:
- Two tables are created:
parent_table
with a primary keyparent_id
andchild_table
with a primary keychild_id
and a foreign keyparent_id
referencingparent_id
inparent_table
. - Sample data is inserted into the tables, with a record in
parent_table
withparent_id
1 and two records inchild_table
withchild_id
101 and 102, both referencingparent_id
1. - The
UPDATE
statement changes theparent_id
inparent_table
from 1 to 2. - Due to the
ON UPDATE CASCADE
constraint, theparent_id
inchild_table
is automatically updated to 2 for both records. - The
SELECT
statement confirms that thechild_table
records now referenceparent_id
2, demonstrating the cascading update behavior.
Example 2: Avoiding Update Cascade for Specific Foreign Key
-- Sample Data
INSERT INTO parent_table VALUES (1);
INSERT INTO child_table VALUES (201, 1), (202, 1);
-- Update Parent Record without Cascade
UPDATE parent_table SET parent_id = 3 WHERE parent_id = 1;
-- Display Child Table
SELECT * FROM child_table;
Output:
child_id |
parent_id |
---|---|
201 |
1 |
202 |
1 |
Explanation:
- Sample data is inserted into
parent_table
with a record containingparent_id
1 and intochild_table
with two records havingchild_id
201 and 202, both referencingparent_id
1. - The
UPDATE
statement modifies theparent_id
inparent_table
from 1 to 3. - As there is no
ON UPDATE CASCADE
constraint, thechild_table
records retain their originalparent_id
values, remaining as 1. - The
SELECT
statement displays thechild_table
records, showing that they still referenceparent_id
1, illustrating the lack of cascading update in this scenario.
Conclusion
Overall, the āON UPDATE CASCADEā in PL/SQL is a valuable tool for automating the update of related records in child tables. By using this option, developers can simplify data management, ensure data integrity, and improve overall efficiency in database operations. However, it is essential to use āON UPDATE CASCADEā judiciously, considering the specific requirements of the data model and the potential impact on data relationships.
Contact Us