SQL Server – On Update and on delete casade

Today we are going to understand sql server on update and delete casade properties that updates child table automatically while updating records in parent table. Child table takes reference  of parent table based on primary and FOREIGN KEY concept. Any records inserted in child table should present in parent table otherwise it will throw foreign key error.

SQL Server – On Update and on delete casade with Example

Lets understand this feature with an example, We are creating Orders table as parent table and OrderDetails  as child table with reference to order table.

USE TESTDB
GO

CREATE TABLE [dbo].[Orders](
[OrderID] [int] NOT NULL PRIMARY KEY ,
[ProductDesc] [varchar](100) NOT NULL,
[Quantity] int )
GO

CREATE TABLE [dbo].[OrderDetails](
[ProductDetailID] [int] NOT NULL PRIMARY KEY,
[OrderID] [int] NOT NULL,
[Total] [int] NOT NULL,
)
GO

— Add FOREIGN KEY in child table( OrderDetails)

ALTER TABLE [dbo].[OrderDetails]
ADD CONSTRAINT[FK_OrderDetails_Orders] FOREIGN KEY([OrderID])
REFERENCES [dbo].[Orders] ([OrderID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
— Insert Data into Table
USE abc
GO
INSERT INTO [Orders] ([OrderID],[ProductDesc],[Quantity])
VALUES(101,’Milk’,1),
(102,’Biscuit’,3),
(103,’Pen’,2)
INSERT INTO [OrderDetails]([ProductDetailID],[OrderID],[Total])
VALUES(201,101,1),
(202,102,3),
(203,103,2),
(204,103,1),
(205,103,1),
(206,103,1)

SELECT * FROM Orders
SELECT * FROM OrderDetails

table_output
Delete Data from Orders Table

— Deleting Data
DELETE
FROM Orders
WHERE OrderID = 101
GO

Select Data from Tables Again

— Selecting Data
SELECT *
FROM Orders
SELECT *
FROM OrderDetails
GO

table_output_1

 

Here we can see Order id 101 is deleted automatically from OrderDetails once deleted from Orders table.

Leave a Reply

  

  

  


+ 8 = 11