When to Use "ON UPDATE CASCADE" in PostgreSQL?

In PostgreSQL, the ON UPDATE CASCADE clause in a foreign key constraint allows for a cascading update behavior between linked tables. This feature automatically updates all matching values in the referencing columns of child tables when a value is changed in the referenced column of the parent table.

In this article, We will explore about ON UPDATE CASCADE in maintaining referential integrity and ensuring consistency in relational databases.

What is ON UPDATE CASCADE in PostgreSQL?

• To specify a cascading update behavior between linked tables in PostgreSQL use the ON UPDATE CASCADE clause in a foreign key constraint.
  
• When this clause is used PostgreSQL automatically updates all matching values in the referencing columns of the child tables whenever a value is changed in the referenced column of the parent table.
  
• This helps preserve referential integrity in the database and guarantees that the relationships between the tables stay consistent.
  
• One of PostgreSQL's most useful features is the ON UPDATE CASCADE clause which makes managing related data easier.
  
• When a referenced value in the parent table changes there is no longer a need to manually update linked records in the child tables, which lowers the possibility of inconsistent data.

Let's consider several situations in which ON UPDATE CASCADE may be useful:

1. Update Primary Key

• Suppose We want the matching foreign key values in child tables to be updated when changes are made to a parent table's main key.
  
• To update the user_id in the orders table when the id in the users table is modified, for example if we have a users table with a primary key called id and an orders table with a foreign key called user_id that references the id column in the users table. This may be achieved by using ON UPDATE CASCADE.

Example:

Before updating the changes

--Create Table--
CREATE TABLE parent (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255)
);

CREATE TABLE child (
    id SERIAL PRIMARY KEY,
    parent_id INT REFERENCES parent(id) ON UPDATE CASCADE,
    child_name VARCHAR(255)
);

-- Insert values--
INSERT INTO parent (name) VALUES ('Parent 1');
INSERT INTO child (parent_id, child_name) VALUES (1, 'Child 1');

-- Check child table--
SELECT * FROM child;

Output:

After Updating the changes

-- Update parent primary key--
UPDATE parent SET id = 100 WHERE id = 1;

--Check child table--
SELECT *FROM child;

Output:

Explanation: If we update the id of the parent table, the parent_id in the child table will be automatically updated to maintain the referential integrity.

2. By Upholding Relationships

Making sure that the rows in the child table continue to be associated to the appropriate rows in the parent table.

Example:

-- Create parent table--

CREATE TABLE parent (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL
);

-- Create child table with foreign key referencing parent table--

CREATE TABLE child (
    id SERIAL PRIMARY KEY,
    parent_id INT REFERENCES parent(id) ON UPDATE CASCADE,
    child_name VARCHAR(255) NOT NULL
);

-- Insert values--

INSERT INTO parent (name) VALUES ('Parent 1');
INSERT INTO child (parent_id, child_name) VALUES (1, 'Child 1');
INSERT INTO child (parent_id, child_name) VALUES (1, 'Child 2');

-- Check the child table--
SELECT * FROM child;

Output:

After Updating Parent's Table

-- Update the parent table's primary key--

UPDATE parent SET id = 100 WHERE id = 1;

-- Check the child table--

SELECT * FROM child;

Output:

Explanation: In this example, both rows in the child table that were associated to the parent table's row are updated to reflect the new id when the parent table's id is changed from 1 to 100.

3. Update Foreign Key

Suppose We want to update the corresponding foreign key values in child tables when a foreign key value in the parent table is updated. This comes in handy when there's a requirement to dynamically adjust the relationship between tables.

Example:

--Create Parent table--

CREATE TABLE parent (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255)
);

--Create Child table--

CREATE TABLE child (
    id SERIAL PRIMARY KEY,
    parent_id INT REFERENCES parent(id) ON UPDATE CASCADE,
    child_name VARCHAR(255)
);

-- Insert values--

INSERT INTO parent (name) VALUES ('Parent 1');
INSERT INTO child (parent_id, child_name) VALUES (1, 'Child 1');

-- Check child table--

SELECT * FROM child;

Output:

After Updating Changes

-- Update parent foreign key--

UPDATE parent SET id = 100 WHERE id = 1;

-- Check child table--

SELECT * FROM child;

Output:

Explanation: Referential integrity will be preserved by automatically updating the parent_id in the child_table from 1 to 100 following the update.

Conclusion

Overall, The ON UPDATE CASCADE feature in PostgreSQL is a powerful tool for managing related data in relational databases. By automatically updating referencing columns in child tables when changes occur in the parent table, ON UPDATE CASCADE helps maintain referential integrity and data consistency.