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 is modified.

In this article, we will explore scenarios where using ON UPDATE CASCADE in Oracle is beneficial, along with practical examples to illustrate its advantages.

What is "ON UPDATE CASCADE" in PL/SQL?

The ON UPDATE CASCADE clause is used to maintain referential integrity in relational databases. It is applied as part of a foreign key constraint definition in the child table, ensuring that if the parent table's key is updated, all corresponding foreign keys in the child table are automatically updated. This reduces the risk of data inconsistencies and makes database management more efficient.

We will understand through the below examples.

Setting Up the Environment for ON UPDATE CASCADE

To illustrate the use of ON UPDATE CASCADE, we will set up a basic environment with parent and child tables. 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_table 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

Let’s walk through some examples to understand how ON UPDATE CASCADE works in Oracle and its impact on related records.

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:

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:

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.

Important Points About PL/SQL ON UPDATE CASCADE

• The ON UPDATE CASCADE option is specified when creating or altering a foreign key constraint in the child table
• If ON UPDATE CASCADE is not specified, updates to the parent table will not affect the child table, leaving the foreign key values unchanged.
• In Oracle databases with many interrelated tables, cascading updates may trigger a chain reaction affecting numerous records, which could have unintended consequences.
• Helps avoid situations where child records reference non-existent or outdated parent keys, ensuring data accuracy across related tables.