ON UPDATE CASCADE is a referential integrity constraint option that we can use in SQL Server when creating foreign keys. It automatically updates foreign key values in child tables when the corresponding primary key in the parent table is updated.

How It Works

It’s pretty simple really. It goes like this:

1. When we create a foreign key constraint, we define it with the ON UPDATE CASCADE option.
2. If we ever need to update a primary key value in the parent table, SQL Server automatically updates matching foreign key values in child tables.

This maintains referential integrity without us having to do manual updates to child tables.

Example

Let’s create a simple database to demonstrate.

Create the Tables

Suppose we create the following tables:

-- Create Authors table
CREATE TABLE Authors (
    AuthorID INT PRIMARY KEY,
    AuthorName VARCHAR(100)
);

-- Create Books table with ON UPDATE CASCADE
CREATE TABLE Books (
    BookID INT PRIMARY KEY,
    Title VARCHAR(200),
    AuthorID INT,
    FOREIGN KEY (AuthorID) REFERENCES Authors(AuthorID)
    ON UPDATE CASCADE
);

Here, the Books table is a child of the Authors table. The Books table has a foreign key that references the AuthorId column in the Authors table.

Importantly, when we created the foreign key, we used ON UPDATE CASCADE.

Let’s now insert some data:

-- Insert authors
INSERT INTO Authors (AuthorID, AuthorName) VALUES
(1, 'Sesh Smith'),
(2, 'Blake Wiley');

-- Insert books
INSERT INTO Books (BookID, Title, AuthorID) VALUES
(101, 'The Great Novel', 1),
(102, 'Amazing Stories', 2),
(103, 'Sesh''s Memoir', 1);

-- Check the data
SELECT * FROM Authors;
SELECT * FROM Books;

Output:

BookID  Title            AuthorID
------  ---------------  --------
101     The Great Novel  1       
102     Amazing Stories  2       
103     Sesh's Memoir    1       

So this is the original data. We’ll be updating one of the AuthorId‘s next.

Demonstrating ON UPDATE CASCADE

Let’s update an AuthorID in the Authors table:

UPDATE Authors
SET AuthorID = 3
WHERE AuthorID = 1;

This update will automatically cascade to the Books table, updating all books by Sesh Smith to have an AuthorID of 3.

Verifying the Result

Let’s check the data again:

SELECT * FROM Authors;
SELECT * FROM Books;

Result:

AuthorID  AuthorName 
--------  -----------
2         Blake Wiley
3         Sesh Smith 

BookID  Title            AuthorID
------  ---------------  --------
101     The Great Novel  3       
102     Amazing Stories  2       
103     Sesh's Memoir    3       

We can see that the relevant values have been updated in both the parent table and the child table.

Benefits and Considerations

Here are some of the key benefits of the ON UPDATE CASCADE option, as well as things to consider before implementing it:

• Maintains data integrity automatically
• Reduces manual update efforts
• May have performance impact on large-scale updates
• Careful design needed to avoid unintended cascading effects
• There’s also an ON DELETE CASCADE that deletes child data whenever related parent data is deleted.

When to Use

• One-to-many relationships where child records should always reflect parent key changes
• Scenarios where data consistency is critical and manual updates are error-prone

Remember to test thoroughly in non-production environments before implementing in critical systems.