Cascade in SQL

Structured Query Language (SQL) is the backbone of relational database management systems, Enabling developers to manipulate and manage data effectively. One crucial feature for maintaining data integrity in SQL is the CASCADE operation, which simplifies handling relationships between parent and child tables.

This article explains SQL CASCADE, its types, with simple examples and outputs. It is designed to help us easily understand and apply it in our database, while being optimized with SEO keywords for better visibility.

What is Cascade?

In SQL, the term CASCADE refers to an action triggered automatically when a change occurs in a parent table, propagating the update or deletion to the related child table(s). By using ON DELETE CASCADE or ON UPDATE CASCADE, we can ensure referential integrity, maintain consistent relationships, and reduce the complexity of manual database management tasks.

Key Features of CASCADE

1. Simplifies Data Management: Automates cascading changes across tables.
   
2. Ensures Referential Integrity: Maintains consistent relationships between parent and child tables.
   
3. Optimizes Database Design: Reduces manual interventions for managing dependent records.

Examples of SQL Cascade

Let's create the database in SQL server management studio and then create a a parent table (which contains the primary key) and child table (which contains the foreign key) in the SQL server and insert some data into it and then we will perform different cascade operations into it.

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 Doe');
INSERT INTO Authors (AuthorID, AuthorName) VALUES (2, 'Minal Pandey');
INSERT INTO Authors (AuthorID, AuthorName) VALUES (3, 'Mahi Pandey');

Step 4: View All Records in the Authors Table

SELECT * FROM Authors;

Output

Step 5: 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
);

Step6: 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 4: View All Records in the Books Table

SELECT * FROM Books;

Output

Types of Cascade

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

In the below given query we are deleting a record from the author's table where authored is 2. The books associated with authored 2 will automatically get deleted.

DELETE FROM Authors WHERE AuthorID = 2;

Output

In the books table also, the books associated with AuthorID = 2 (Minal Pandey) has been deleted.

2. ON UPDATE CASCADE

We will use this type of cascade when a primary key in the parent table is updated. In such cases, the corresponding foreign key values in the child table are automatically updated.

Example

In the query below, we are updating AuthorID = 1 to AuthorID = 2. As a result, all rows where AuthorID = 1 will be updated to AuthorID = 2.

UPDATE Authors SET AuthorID = 1 WHERE AuthorID = 2;

Output

3. ON INSERT CASCADE

By using ON INSERT Cascade we can inserts records into related tables when a new record is added to the parent table.

Example

In this query, we are inserting a new author with id 4 and a new book associated with the new author.

INSERT INTO Authors (AuthorID, AuthorName) VALUES (4, 'Sukumar Reddy');
INSERT INTO Books (BookID, Title, AuthorID) VALUES (105, 'Data Science', 4);

Output

Conclusion

SQL CASCADE is an important tool for maintaining referential integrity and simplifying database management. By understanding and using the ON DELETE CASCADE, ON UPDATE CASCADE, and simulated ON INSERT CASCADE, developers can create efficient and consistent databases. However, it's essential to use these features judiciously to avoid performance issues and unintended data loss.