Click here to Skip to main content
14,666,126 members
Articles » Database » Database » SQL Server » Revisions
Technical Blog
Posted 12 Jul 2013

Tagged as

Stats

178.2K views
10 bookmarked

CASCADE in SQL Server with example

Rate this:
4.24 (14 votes)
Please Sign up or sign in to vote.
4.24 (14 votes)
12 Jul 2013CPOL
Use the ON DELETE CASCADE option if you want rows deleted in the child table when corresponding rows are deleted in the parent table.
This is an old version of the currently published technical blog.

Use the ON DELETE CASCADE option if you want rows deleted in the child table when corresponding rows are deleted in the parent table. If you do not specify cascading deletes, the default behavior of the database server prevents you from deleting data in a table if other tables reference it.

If you specify this option, when you delete a row in the parent table, the database server also deletes any rows associated with that row (foreign keys) in a child table. The advantage of the ON DELETE CASCADE option is that it allows you to reduce the quantity of SQL statements needed to perform delete actions.

select * from dbo.ProductDetails
select * from dbo.Products

CREATE TABLE [dbo].[Products](
[ProductID] [int] NOT NULL,
[ProductDesc] [varchar](50) NOT NULL,
CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED
(
[ProductID] ASC
)) ON [PRIMARY]

CREATE TABLE [dbo].[ProductDetails](
[ProductDetailID] [int] NOT NULL,
[ProductID] [int] NOT NULL,
[Total] [int] NOT NULL,
CONSTRAINT [PK_ProductDetails] PRIMARY KEY CLUSTERED
(
[ProductDetailID] ASC
)) ON [PRIMARY]
GO

ALTER TABLE [dbo].[ProductDetails] WITH CHECK ADD CONSTRAINT 
[FK_ProductDetails_Products] FOREIGN KEY([ProductID])
REFERENCES [dbo].[Products] ([ProductID])
ON UPDATE CASCADE
ON DELETE CASCADE

INSERT INTO Products (ProductID, ProductDesc)
SELECT 1, 'Bike'
UNION ALL
SELECT 2, 'Car'
UNION ALL
SELECT 3, 'Books'

INSERT INTO ProductDetails
([ProductDetailID],[ProductID],[Total])
SELECT 1, 1, 200
UNION ALL
SELECT 2, 1, 100
UNION ALL
SELECT 3, 1, 111
UNION ALL
SELECT 4, 2, 200
UNION ALL
SELECT 5, 3, 100
UNION ALL
SELECT 6, 3, 100
UNION ALL
SELECT 7, 3, 200

SELECT *
FROM Products
SELECT *
FROM ProductDetails

DELETE
FROM Products
WHERE ProductID = 1

DROP TABLE ProductDetails
DROP TABLE Products

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

vinothharshad
Software Developer
India India
This is Vinoth from Coimbatore. Vinoth have been working as software developer since Jan 2011. Find Vino's other Interesting article in http://dotnettips4all.blogspot.in/

Comments and Discussions

Discussions on this specific version of this article. Add your comments on how to improve this article here. These comments will not be visible on the final published version of this article.