There are many situations when we want to perform some action on foreign key value when primary key values get deleted or updated. If you wants to do this then do read this article as you are at the right place.
Cascading delete and update uses DRI (declarative referential integrity), it enforce integrity without writing any additional trigger or code. It is much faster than trigger as well.
You can perform few different operations when cascading event gets fire for update or delete.
if you don’t want to let user delete or modify the data in primary key when its foreign key data exist than do use this action.
If your record of primary key gets deleted or updated than “SET NULL” action will update foreign key value with NULL. There is one condition for this; foreign key column should be set to accept NULL value.
if your record of primary key gets deleted then related records from foreign key table will be deleted and if your record of primary key table gets updated then related records from foreign key table will be updated with new values.
if records in primary key will be deleted or updated and you have its child data available then it will be set by column’s default value.
Let us create two tables and see how CASCADE works on that.
–create first table with primary key
CREATE TABLE Orders
OrderID VARCHAR(5) NOT NULL CONSTRAINT PK_Orders_OrdersID PRIMARY KEY(OrderID),
OrderDate DATETIME NOT NULL DEFAULT GETDATE()
–create second table with foreign key and CASCADE action ON DELETE and ON UPDATE
CREATE TABLE OrderDetails
OrderID VARCHAR(5) NOT NULL CONSTRAINT FK_OrderDetails_OrderID
FOREIGN KEY(OrderID) REFERENCES Orders(OrderID) ON DELETE CASCADE ON UPDATE CASCADE,
CustomerName VARCHAR(10) NOT NULL,
Quantity INT NOT NULL
–insert data in both tables
INSERT INTO Orders VALUES (‘A1000’,GETDATE())
INSERT INTO OrderDetails VALUES(‘A1000’,‘Ritesh’,10)
Now let us try to update or delete record in first table “Orders”.
–observe both table before doing any action.
select * from Orders
Select * from OrderDetails
–try to update record, it will take effect in child table as well,
–without even writing any code or trigger
update Orders SET OrderID=‘A1001’ WHERE OrderID=‘A1000’
Now, again try to see both the table and you will find updated records in both table. Let us try by deleting records.
–this delete statement will delete all records of A1001 orderid from both tables
Delete From Orders where OrderID=‘a1001’
Reference: Ritesh Shah