Cascade in SQL

Structured Query Language (SQL) is a powerful tool for managing and manipulating relational databases. Maintaining data integrity is essential to database administration, particularly when working with table relationships. In this article, we can go through the idea of SQL CASCADE, a function that performs a considerable function in retaining referential integrity inside a database. The cascading referential integrity constraints in SQL servers are the foreign key constraints that tell SQL servers to perform certain actions whenever a user attempts to delete or update a primary key to which an existing foreign key points.

What is Cascade?

In SQL, CASCADE is used to update or remove an entry from both the parent and child tables at the same time. The ON DELETE or ON UPDATE query uses the phrase CASCADE as a conjunction. If a user tries to delete a statement that will affect the rows in the foreign key table, then those rows will be deleted when the primary key record is deleted. Similarly, if an update statement affects rows in a foreign key table, then those rows will be updated with the value from the primary key record after it has been updated.

There are different types of cascade, each having a specific purpose in maintaining the consistency of data relationships.

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.

Create Database

CREATE DATABASE Cascading;
USE Cascading;

Create a Parent Table

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

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
);

Output:

You can see that the database and table were created successfully.

Database and Tables

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');

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);

Now check whether the data is inserted or not using the below query.

SELECT * FROM Authors;
SELECT * FROM Books;

Output:

Books Table

Authors table

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:

Now you can check in authors table.

Authors table

In the books table also, the books associated with AuthorID 2 will be 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 below query, we are updating the authorID 1 to 2. You can see wherever the authored is 1 it will be set to 2.

UPDATE Authors SET AuthorID = 1 WHERE AuthorID = 2;

Output:

Authors and Book table

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);

You can see the below output record is inserted successfully.

Output:

ON INSERT

Conclusion

In the end, expertise and utilizing SQL CASCADE is critical for constructing robust and reliable database systems. The various forms of CASCADE, ON DELETE CASCADE, ON UPDATE CASCADE, and ON INSERT CASCADE, offer flexibility in retaining referential integrity. By incorporating these features into database design, developers can create systems that mechanically deal with modifications in relationships, reducing the chance of data inconsistencies and making sure a greater seamless and efficient data management process.