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 key parent_id and child_table with a primary key child_id and a foreign key parent_id referencing parent_id in parent_table.
• Sample data is inserted into the tables, with a record in parent_table with parent_id 1 and two records in child_table with child_id 101 and 102, both referencing parent_id 1.
• The UPDATE statement changes the parent_id in parent_table from 1 to 2.
• Due to the ON UPDATE CASCADE constraint, the parent_id in child_table is automatically updated to 2 for both records.
• The SELECT statement confirms that the child_table records now reference parent_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 containing parent_id 1 and into child_table with two records having child_id 201 and 202, both referencing parent_id 1.
• The UPDATE statement modifies the parent_id in parent_table from 1 to 3.
• As there is no ON UPDATE CASCADE constraint, the child_table records retain their original parent_id values, remaining as 1.
• The SELECT statement displays the child_table records, showing that they still reference parent_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.