SQL Server allows you to define cascading referential integrity constraints. Cascading referential integrity constraints allow you to define the actions when a user tries to delete or update a key for which foreign keys exist. Cascading is used with the drop command when we want to drop a parent table even when a child table exists. If you execute a delete command without a cascading constraint then it will show 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 2012 using the SQL Server Management Studio.
Creating tables in SQL Server
First we create two tables named MajorCategory and MinorCategory.
MajorCategory Table
I have created a Table named MajorCategory which has three columns named CategoryID, CategoryName and CategoryDescription. Set the identity and primary key properties of the CategoryID; see:
CREATE TABLE [dbo].[MajorCategory](
[CategoryId] [int] IDENTITY(1,1) NOT NULL,
[CategoryName] [varchar](200) NULL,
[CategoryDescription] [nvarchar](200) NOT NULL,
CONSTRAINT [PK_MajorCategory] PRIMARY KEY CLUSTERED
(
[CategoryId] 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 table MajorCategory looks as in the following:
![Major-Category-Table-in-SQL-Server.jpg]()
MinorCategory Table
I have created a Table named MinorCategory which has four columns named MinorCategoryID, CategoryID, SubCategoryName and CategoryDescription. Set the identity and referential integrity properties of the MinorCategoryID.
CREATE TABLE [dbo].[MinorCategory](
[MinorCategoryId] [int] IDENTITY(1,1) NOT NULL,
[CategoryId] [int] NOT NULL,
[SubCategoryName] [varchar](200) NULL,
[SubCategoryDescription] [nvarchar](200) NOT NULL,
CONSTRAINT [PK_MinorCategory] PRIMARY KEY CLUSTERED
(
[MinorCategoryId] 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].[MinorCategory] WITH CHECK ADD CONSTRAINT [FK_MinorCategory_MajorCategory] FOREIGN KEY([CategoryId])
REFERENCES [dbo].[MajorCategory] ([CategoryId])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[MinorCategory] CHECK CONSTRAINT [FK_MinorCategory_MajorCategory]
GO
Table MinorCategory looks as in the following:
![Minor-Category-Table-in-SQL-Server.jpg]()
Delete Statement
Now, we try to delete our majorcategory which defines the foreign key constraints. It displays the following error:
delete from MajorCategory where Categoryid='21'
![Delete-Major-Category-Table-row-in-SQL-Server.jpg]()
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; see:
CREATE TABLE [dbo].[MinorCategory](
[MinorCategoryId] [int] IDENTITY(1,1) NOT NULL,
[CategoryId] [int] NOT NULL,
[SubCategoryName] [varchar](200) NULL,
[SubCategoryDescription] [nvarchar](200) NOT NULL,
CONSTRAINT [PK_MinorCategory] PRIMARY KEY CLUSTERED
(
[MinorCategoryId] 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].[MinorCategory] WITH CHECK ADD CONSTRAINT [FK_MinorCategory_MajorCategory] FOREIGN KEY([CategoryId])
REFERENCES [dbo].[MajorCategory] ([CategoryId])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[MinorCategory] CHECK CONSTRAINT [FK_MinorCategory_MajorCategory]
GO
Now we delete a row from the major table:
delete from MajorCategory where Categoryid='21'
![delete-row-withMajor-Category-Table-in-SQL-Server.jpg]()