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.

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:

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:

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.