Cascade in SQL

Introduction

CASCADE is a keyword in MySQL relational database management system. Let us take an example in layman's terms to understand the usage of cascade keyword in SQL.

Consider a table named T1 which contains the data of all the students in a class. Table T1 has four columns, i.e., Enrollment number, name, email, and state of the student. Let us now consider two more tables, i.e., T2 and T3. Table T2 contains the marks of all the students in the Mathematics subject. Table T3 contains the marks of all the students in the Science subject. Both tables, T2 and T3, have three columns, i.e., enrollment number, name of the students, and marks of the student. Now, if there is a case where a student wants to change a class or change his name/enrollment number, we need to make certain changes in the database.

Following is the design of our MySQL database (arrangement of tables).

Case 1: If a student wants to change his class, he must delete his name from all the tables, i.e., T1, T2, and T3. As the student will be shifted to another class, so his information needs to be deleted.

Case 2: If a student wants to change his name/enrollment number, he has to update all three tables, i.e., T1, T2, and T3.

If we try to perform the scenarios provided in the above cases in a traditional fashion, the changes would need to be done manually in all three tables. We use the keyword CASCADE to overcome this problem in SQL queries. With a single command, we can change all three tables simultaneously.

Using a similar example, we will learn how to use CASCADE in SQL while deleting/updating an entry in the parent and child table. To do so, we will define our database more efficiently.

Setting up Database

As we have three tables, T1, T2, and T3, each table should be in the following format.

Table T1

Primary key: Enrollment number (int); name (varchar(20)), email-id (varchar(20)), and State of the student (varchar(20)) are regular columns.

Tables T2 & T3

Primary key: Enrollment number (int), name (varchar(20)); Marks of the student (int) is a regular column.

Creating Table T1

To see all the columns created in Table T1, run the following command.

Creating Table T2 and T3.

As we can see, while creating tables T2 and T3, we added the keywords ON DETELE CASCADE and ON UPDATE CASCADE on the line where the FOREIGN KEY is declared.

Lets us check all the columns from table T2 and T3.

Let us insert some data into the table T1 (Students in the class).

Use the following query to view all the data of the Class table.

Now, insert data into tables T2 and T3.

Inserting values into the Science table.

Printing all the data from the Science table.

Inserting values into the Mathematics table.

Printing all the data from the Mathematics table.

We have created tables T1, T2, and T3 and added the data to the tables.

ON DELETE CASCADE in SQL

In this section, we will discuss the ON DELETE CASCADE method to solve Case 1, which we have seen in the introduction. If the student Rishab P changes his Class, then his name must be removed from all the tables, i.e., T1, T2, and T3.

Deleting an entry of a particular student from all three tables needs three queries to be executed, but while setting up the database of tables T2 and T3, we have used an ON DELETE CASCADE keyword. Because of the CASCADE keyword, we can delete the entry from the Parent Table T1, and it will automatically delete the name of the student from the table T2 and T3.

Example of ON DELETE CASCADE in SQL

Deleting the entry of student Rishab P from the Class table.

As we have only deleted the entry from the Class table, it will automatically reflect the changes in the other two tables also.

Displaying the data of the Class table.

Displaying the data of Mathematics table.

Displaying the data of Science table.

As we can see from the above output, the entry of the student named Rishab P has been removed from all three tables.

ON UPDATE CASCADE in SQL

The student with enrollment number 1 was deleted when we executed the query in the previous section. So, we will now update the enrollment number of the last student in the table to the value 1. The last student has an enrollment number equal to 5 currently. The entry for the student with enrollment number 5 is present in two tables, i.e., the Class and Science table.

To update both tables traditionally, it will take two SQL queries. But, as we have added a keyword, ON UPDATE CASCADE while setting up the parent table's database, the Science table entry will automatically get updated.

Example of ON UPDATE CASCADE in SQL

Updating the enrollment number of the student having enrollment 5 (updating it to 1).

We have only updated the entry from the Class table, but we can verify that it will also automatically reflect the changes in the other tables.

Displaying the data of the Class table.

Displaying the data of Science table.

As we can see in the above table that the student having the enrollment 5 is changed it enrollment to 1.

ON INSERT CASCADE in SQL

Many people find this title amusing since it refers to a fictitious feature that people wrongly mention when discussing cascade constraints. The main idea of using ON INSERT CASCADE is that whenever a data entry is made in the parent table, and we also want to make the same entry in the child table, we use this cascade. This part will take a different example to understand ON INSERT CASCADE.

Example of ON INSERT CASCADE in SQL

First, we will create two tables, i.e., item and stock. The first table item will be the parent table, and the second table stock will be the child table. Whenever we make an entry in the item table, the stock table gets updated automatically by entering new data.

Query to create Item Table

Query to create Stocks Table

Here, the item table has three columns, i.e., Id (Primary Key), Name, and the product's price. The stock table has two columns, i.e., Id(Primary Key, Foreign Key reference from Id of item table), and Quantity.

Now, whenever a new product is launched, we need to insert the product name into the item table and set that item's quantity to zero in the stocks table. Manually this can be done by executing two queries, but using the ON INSERT CASCADE technique, this can be done within a single query.

To do so, we need to use TRIGGERS.

Read more - TRIGGERS in SQL

Implementing ON INSERT CASCADE

The above query will automatically insert the data into the stocks table.

Inserting Data

Inserting new product in the item table.

Now, we will display both tables. Displaying item table.

Displaying stocks table.

As we can see, a single insert query is used to add data to both tables.