Cascading referential integrity constraint in Sql Server

Hi friends! In our previous sessions we have seen how to create tables and enforce primary and foreign key constraints between them. And in our last session we learnt adding a default constraint in SQL Server.

Now, Today we are going to learn Cascading referential integrity constraint in SQL Server

SQL Server defines many cascading referential integrity constraints to choose from. Cascading referential integrity constraints allow you to define the actions SQL Server should take when a user tries to delete or update a key for which foreign keys exist in the database.

Cascading is used with the drop command. Suppose, we want to drop a record from the parent table, even when we have the corresponding records in the child table or we want to drop a parent table, even when a child table exists, then we use Cascading referential integrity constraints.

If you execute a delete command without a cascading constraint, then it will throw an error.

So let's take a look at a practical example of how to use a SQL Cascading Constraint in SQL Server. The example is developed in SQL Server 2016 using the SQL Server Management Studio.

Firstly, we will create two tables named Category and SubCategory.

Category Table:
Here, I have created a Table named as Category which has three columns named Id, Name, Description. Set the identity and Primary Key properties of the Id as follows:

CREATE TABLE [dbo].[Category](
           [Id] [int] IDENTITY(1,1) NOT NULL,
           [Name] [varchar](200) NULL,
           [Description] [nvarchar](200) NOT NULL,
CONSTRAINT [PK_Category] PRIMARY KEY CLUSTERED
(
           [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

The Category table looks as in the following:

Category Table Structure

SubCategory Table:
Here, I have created a Table named SubCategory which has four columns named Id, CategoryId, Name and Description. Set the identity and Referential Integrity properties of the Id as follows.

CREATE TABLE [dbo].[SubCategory](
           [Id] [int] IDENTITY(1,1) NOT NULL,
           [CategoryId] [int] NOT NULL,
           [Name] [varchar](200) NULL,
           [Description] [nvarchar](200) NOT NULL,
CONSTRAINT [PK_SubCategory] PRIMARY KEY CLUSTERED
(
           [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[SubCategory]  WITH CHECK ADD  CONSTRAINT [FK_SubCategory_Category] FOREIGN KEY([CategoryId])
REFERENCES [dbo].[ Category] ([Id])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[SubCategory] CHECK CONSTRAINT [FK_SubCategory_Category]
GO

Table SubCategory looks as in the following:

Now, let’s try to delete our Category which defines the Foreign Key Constraints.
We could see that, it displays the following error:

Delete from Category where Id='21'

Error while deleting a Primary Key which is used as a Foreign Key in another table

Cascading Referential Integrity Constraints
Now use the ON DELETE CASCADE option to specify whether you want rows deleted in a child table when corresponding rows are deleted in the parent table:

CREATE TABLE [dbo].[SubCategory](
           [Id] [int] IDENTITY(1,1) NOT NULL,
           [CategoryId] [int] NOT NULL,
           [Name] [varchar](200) NULL,
           [Description] [nvarchar](200) NOT NULL,
CONSTRAINT [PK_SubCategory] PRIMARY KEY CLUSTERED
(
           [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[SubCategory]  WITH CHECK ADD  CONSTRAINT [FK_SubCategory_Category] FOREIGNKEY([CategoryId])
REFERENCES [dbo].[Category] ([Id])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[SubCategory] CHECK CONSTRAINT [FK_SubCategory_Category]
GO

Now we delete a row from the major table:

delete from Category where Id='21'

This statement will successfully delete your Category with Id=21

However, you have the following options when setting up Cascading referential integrity constraint

1. No Action: This is the default behaviour of SQL Server. No Action specifies that you try to DELETE or UPDATE a row with a key that is referenced by the foreign keys in other tables, then an error is raised by  SQL Server and the DELETE or UPDATE query is rolled back.

2. Cascade: It specifies that if an attempt is made to DELETE or UPDATE a row with a key referenced by foreign keys in existing rows in other tables, all rows containing those foreign keys are also DELETED or UPDATED in both the tables.

3. Set NULL: It specifies that if an attempt is made to DELETE or UPDATE a row with a key referenced by foreign keys in existing rows in other tables, then all rows containing those foreign keys are set to NULL.

4. Set Default: It specifies that if an attempt is made to DELETE or UPDATE a row with a key referenced by foreign keys in existing rows in other tables, all rows containing those foreign keys are set to default values(if a default constraint is defined in that table).

If you need help in how to create a default constraint in SQL Server table then please refer this link, click here.

Summary:
So, Guys this is all about Cascading referential integrity constraint in SQL Server.
I Hope in this post covered all the points about Cascading referential integrity which will be helpful to understand the concept how useful Foreign keys are.

Please share this post with your friends and colleagues.
For any queries please post a comment below.

Happy Coding 😉