Cascading Delete and Cascading Update constraints in Microsoft SQL Server 2005

Tuesday, March 17, 2009 |

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.

NO ACTION
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.

SET NULL
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.

CASCADE
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.

SET DEFAULT
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

0 comments: