Cascade in SQL

SQL provides the CASCADE operation to automatically apply changes from a parent table to related child tables. It helps maintain data integrity and simplifies the management of relationships between tables.

• It automatically propagates changes like DELETE or UPDATE to related tables.
• It ensures referential integrity between parent and child tables.
• It reduces manual effort in handling dependent records.
• It supports options like ON DELETE CASCADE and ON UPDATE CASCADE.

Examples of SQL Cascade

Let's create a database in SQL Server Management Studio, followed by creating a parent table (containing a primary key) and a child table (containing a foreign key). After inserting data into both tables, we will perform different cascade operations to observe how changes in the parent table affect the related records in the child table

Step 1: Create Database

CREATE DATABASE Cascading;
USE Cascading;

Step 2: Create a Parent Table

CREATE TABLE Authors (
 AuthorID INT PRIMARY KEY,
 AuthorName VARCHAR(200)
);

Step 3: Insert data into Authors table

INSERT INTO Authors (AuthorID, AuthorName) VALUES (1, 'John Smith');
INSERT INTO Authors (AuthorID, AuthorName) VALUES (2, 'Emily Johnson');
INSERT INTO Authors (AuthorID, AuthorName) VALUES (3, 'Michael Brown');

Step 4: View All Records in the Authors Table

SELECT * FROM Authors;

Output:

Step 6: Create a Child Table

CREATE TABLE Books (
 BookID INT PRIMARY KEY,
 Title VARCHAR(255),
 AuthorID INT,
 FOREIGN KEY (AuthorID) REFERENCES Authors(AuthorID) ON DELETE CASCADE
);

Step 7: Insert data into the Books table, automatically linked to the Authors table

INSERT INTO Books (BookID, Title, AuthorID) VALUES (101, 'Introduction to SQL', 1);
INSERT INTO Books (BookID, Title, AuthorID) VALUES (102, 'Database Fundamentals', 2);
INSERT INTO Books (BookID, Title, AuthorID) VALUES (103, 'Advanced SQL', 2);
INSERT INTO Books (BookID, Title, AuthorID) VALUES (104, 'Web Development', 3);

Step 8 : View All Records in the Books Table

SELECT * FROM Books;

Output:

Types of Cascade

SQL supports cascade operations to propagate changes to related tables.

1. ON DELETE CASCADE

ON DELETE CASCADE ensures that when a record in the parent table is deleted, the corresponding records in the child table automatically get deleted.

Example: The following query deletes the record from the Authors table where AuthorID = 2. All related records in the Books table are automatically deleted.

Query:

DELETE FROM Authors WHERE AuthorID = 2;

Output:

2. ON UPDATE CASCADE

ON UPDATE CASCADE ensures that when a primary key in the parent table is updated, the corresponding foreign key values in the child table are automatically updated.

Example: The following query updates AuthorID from 1 to 2 and all related rows in the child table are updated automatically.

Query:

UPDATE Authors SET AuthorID = 1 WHERE AuthorID = 2;

Output: