As a database developer or administrator, you must have encountered the term Cascade in SQL Server. Cascading is a powerful feature that helps maintain data integrity by automatically propagating changes across related tables in a database. This article will explore the following:
- What is cascade in SQL Server?
- Why you should use it.
- How to create a foreign key with delete and update cascade rules.
We will also provide examples and explanations to help you understand the concept better.
Table of Contents
- 1 What is Cascade in SQL Server?
- 2 Why use Cascading in SQL Server?
- 3 SQL Delete Cascade
- 4 Row deletion actions in the parent table
- 5 On Update Cascade in SQL Server
- 6 FAQs
- 6.1 Q: What is the purpose of cascade in SQL?
- 6.2 Q: How does delete cascade work in SQL?
- 6.3 Q. What is the difference between ON DELETE CASCADE and ON UPDATE CASCADE?
- 6.4 Q. Can cascade rules be defined for multiple foreign keys in a table?
- 6.5 Q. What happens if a record in a child table references a record in the parent table that does not exist?
- 7 Conclusion:
What is Cascade in SQL Server?
In SQL Server, cascade automatically propagates changes made to a parent table to its related child tables.
That means when you update or delete a record in the parent table, the corresponding records in the child tables will also be updated or deleted automatically.
Cascade ensures that the relationships between tables are maintained and data integrity is preserved.
The syntax for defining cascade rules in SQL Server is as follows:
CREATE TABLE ParentTable ( ParentID INT PRIMARY KEY, Column1 VARCHAR(50), Column2 INT ) CREATE TABLE ChildTable ( ChildID INT PRIMARY KEY, ParentID INT FOREIGN KEY REFERENCES ParentTable(ParentID) ON DELETE CASCADE ON UPDATE CASCADE, Column1 VARCHAR(50), Column2 INT )
In the above example, we have defined a parent table (ParentTable) with three columns, including a primary key column (ParentID).
We have also defined a child table (ChildTable) with four columns, including a foreign key column (ParentID) that references the primary key column (ParentID) of the parent table.
The ON DELETE CASCADE and ON UPDATE CASCADE clauses specify the cascade rules for the foreign key constraint. When a record in the parent table is deleted or updated, the corresponding records in the child table will be deleted or updated automatically.
Why use Cascading in SQL Server?
There are several benefits of using cascading in SQL Server:
- Data integrity: Cascading helps maintain data integrity by ensuring that related data is consistent and up-to-date.
- Efficiency: Cascading eliminates the need to manually update or delete related records in child tables, saving time and effort.
- Simplicity: Cascading simplifies database management by automating the process of maintaining related data.
- Accuracy: Cascading reduces the risk of errors when manually updating or deleting related records in child tables.
SQL Delete Cascade
In this post, we will look at SQL DELETE CASCADE and UPDATE CASCADE rules in SQL Server foreign keys using multiple examples.
DELETE CASCADE: When we create a foreign key using delete cascade, it deletes the referencing rows in the child table when the referenced row in the parent table with a primary key is deleted.
That means no orphan row in the Child table will be left if a Parent row is deleted. All children`s rows that refer to the parent’s rows should also be deleted (removed). If any of these children have grandchildren (through another foreign key in another table) and ON DELETE CASCADE is defined, they must also be deleted.
Below is a SQL script that creates a parent table (Departments), a child table (Employees), and a foreign key to the child table without using the SQL delete cascade rule.
-- Create Departments table Create table Departments ( Id int primary key identity, [DeptName] nvarchar(50) ) Go -- Create Employees table Create table Employees ( Id int primary key identity, [Name] nvarchar(50), [City] nvarchar(50), DeptId int constraint FK_Key foreign key references Departments(Id) ) Go -- Insert records into Departments table Insert into Departments values ('IT') Insert into Departments values ('Sales') Go -- Insert records into Employees table Insert into Employees values ('Shekh Ali','New Delhi', 1), ('Mark Adam','New York', 1), ('Julie Folden','Paris', 2), ('Amit Kumar','Mumbai', 2), ('Priyanka','Noida', 2); -- Select all the records from Departments and Employees table. SELECT * FROM Departments; SELECT * FROM Employees;
After running the above SQL script, the following
Employees tables will be created in the database.
When we delete a row from the Departments table, all the related rows from the Employees table must also be deleted.
For example, if we delete the IT department row from the Departments (Parent) table, we also want all the employees of the IT department to be deleted from the Employees (Child) table as well.
Here, the DeptId column in the Employees table is a foreign key that is referencing the Id (Primary key) column in the Departments table.
Let’s try to delete the IT department row from the Department’s table by running the following SQL script.
-- Delete IT department DELETE FROM Departments WHERE Id = 1;
If we try to delete a row from the Departments table which has related rows in the Employees table, by default, we get the following reference constraints error message:The DELETE statement conflicted with the REFERENCE constraint “FK_Key”. The conflict occurred in database “TestDB”, table “dbo.Employees”, column ‘DeptId’.
. The statement has been terminated.
Example: SQL Delete Cascade
To apply the SQL
on delete cascade rule, first remove the current foreign key constraint from the Employees (Child) table. Here is a SQL script that will drop the foreign key constraint from the child table.
-- Drop foreign key from the Employees table ALTER TABLE Employees DROP CONSTRAINT FK_Key;
Now let’s recreate the foreign key constraint with cascading delete rule in the Employees table.
-- Create foreign key constraint with delete cascade ALTER TABLE Employees ADD CONSTRAINT FK_Dept_Emp_Cascade_Delete FOREIGN KEY (DeptId) REFERENCES Departments(Id) ON DELETE CASCADE;
Now when we delete a row from the Departments table, all the related rows from the Employees table will also get deleted.
-- Delete IT department DELETE FROM Departments WHERE Id = 1; -- Select all the records from Departments and Employees table. SELECT * FROM Departments; SELECT * FROM Employees;
Once we run the above SQL query, the IT department will get deleted from the Departments table, and also all the referenced rows will be deleted from the Employees table.
In the following image, we can see that the IT department is no more available in the Departments table and referenced rows from the Employees table will be cleaned up automatically.
Row deletion actions in the parent table
When you delete one or more rows from the parent table, you have the option of performing one of the following actions:
|ON DELETE NO ACTION:||SQL Server raises an error and rolls back the delete action on the record in the parent table.|
|ON DELETE CASCADE:||When a row in the parent table is deleted, SQL Server deletes the corresponding row in the child table.|
|ON DELETE SET NULL:||SQL Server sets the rows in the child table to NULL when the corresponding rows in the parent table are deleted. The foreign key columns must be nullable in order for this action to be performed.|
|ON DELETE SET DEFAULT:||When the rows in the parent table are deleted, SQL Server update the rows in the child table to their default values. This process can only be carried out if the foreign key columns contain default definitions.|
If you don’t explicitly specify an action, SQL Server employs
ON DELETE NO ACTION by default.
On Update Cascade in SQL Server
UPDATE CASCADE: When we create a foreign key using
UPDATE CASCADE, the referencing rows in the child table are updated whenever the referred row in the parent table with a primary key is updated.
ALTER TABLE [dbo].[tblChild] ADD CONSTRAINT FK_tblChild FOREIGN KEY([ID]) REFERENCES [dbo].[tblParent]([ID]) ON UPDATE CASCADE
Q: What is the purpose of cascade in SQL?
In SQL, CASCADE is used in conjunction with ON DELETE or ON UPDATE. It means that when the parent row is deleted or updated, the child row is also deleted or updated simultaneously.
Q: How does delete cascade work in SQL?
Use the ON DELETE CASCADE option to specify whether to delete child table rows when matching rows in the parent table are deleted. If you don’t define cascading deletes, the database server prevents you from deleting data in a table when the table is referenced by other tables.
Q. What is the difference between ON DELETE CASCADE and ON UPDATE CASCADE?
ON DELETE CASCADE specifies that the related records in child tables should be deleted when a record in the parent table is deleted, whereas ON UPDATE CASCADE specifies that the related records in child tables should be updated when a record in the parent table is updated.
Q. Can cascade rules be defined for multiple foreign keys in a table?
Yes, we can define cascade rules for multiple foreign keys in a table.
Q. What happens if a record in a child table references a record in the parent table that does not exist?
If a record in a child table references a record in the parent table that does not exist, it will result in a foreign key constraint violation error.
Cascade in SQL Server is a powerful feature that helps maintain data integrity by automatically propagating changes across related tables in a database. It’s an efficient and simple way to manage related data and maintain relationships between tables.
However, it’s important to use cascade rules carefully and understand their impact on the database performance.
I hope you enjoyed and learned something from this article. if you have any suggestions, questions, or comments, please share them with us.
- Having VS Where Clause in SQL
- SQL EXISTS – Exploring EXISTS Operator in SQL Server
- Like operator in SQL
- COALESCE in SQL With Examples and Use Cases for Handling NULL Values
- SQL Server CONVERT Function: How to Convert Data Types in SQL Server
- Delete vs Truncate vs Drop in SQL Server
- SQL Server TRY_CAST() Function – Understanding TRY_CAST() in SQL with [Examples]
- SQL Server Try Catch: Error Handling in SQL Server With [Examples]
- Triggers in SQL Server
- Different ways to delete duplicate rows in SQL Server
- Types of Joins in SQL Server
- Function vs Stored procedure in SQL Server
- UNION vs UNION ALL in SQL SERVER
- View In SQL | Types Of Views In SQL Server
- SQL pivot tables: Understanding pivot tables in SQL Server
- Group by Month in SQL: A Comprehensive Guide with Examples in SQL Server
- Stored Procedure in SQL Server With Examples
- CREATE, ALTER, AND DROP DATABASE IN SQL SERVER
- Primary Key Constraint In SQL Server
- SQL Comparison Operators
- Understanding the SOLID Principle: Single Responsibility Principle in C# - June 7, 2023
- Difference between var and dynamic in C# - May 26, 2023
- Understanding the Chain of Responsibility Design Pattern in C# - May 11, 2023