Cascading Referential Integrity Constraint in SQL Server

Introduction

This article will discuss Cascading Referential Integrity Constraints in SQL Server. SQL Server allows you to define cascading referential integrity constraints.

What are Integrity Constraints in SQL Server?

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 look at a practical example of how to use a SQL Cascading Constraint in an 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.

MinorCategory Table

I have created a Table named MinorCategory, with 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 the following.

Delete Statement

Now, we try to delete our major category, which defines the foreign key constraints. It displays the following error.

delete from MajorCategory  where Categoryid='21'

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])

ONDELETE CASCADE

GO ALTER TABLE [dbo].[MinorCategory] CHECK CONSTRAINT [FK_MinorCategory_MajorCategory] GO

Now we delete a row from the significant table.

delete from MajorCategory  where Categoryid='21'

Conclusion

This article taught us about Cascading Referential Integrity Constraints in SQL Server.