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.
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.
Contact Us