Cascading Delete and Cascading Update constraints in Microsoft SQL Server 2005

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

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Difference between Microsoft SQL-Server 2000 and Microsoft SQL-Server2005:

Difference between Microsoft SQL-Server 2000 and Microsoft SQL-Server2005:
This question is one of the mostly asked question in an interview but let me tell you that in depth answer of this question could generate one complete book but let us see the difference here in brief.
Almost features of Microsoft SQL-Server 2000 are available in Microsoft SQL-Server 2005 with some improvement and enhancement along with some new feature. Let us see those in brief.
— GUI improvement (SSMS –SQL Server Management Studio)
— T-SQL enhancement
–CLR integration
–Table Partitioning
–Database Mirroring

–DDL trigger support

Along with “Database Mirroring” as a high availability in Microsoft SQL Server 2005 there are old feature of SQL Server 2000 are also available like replication, clustering and log shipping but it was working for only SQL-Server 2000’s enterprise edition but in Microsoft SQL Server 2005, it is available for standard edition also.
Scalability was limited to 2GB and 4CPU in standard edition of Microsoft SQL Server 2000 but in Microsoft SQL Server 2005, it supports 4 CPU and no RAM limitation in standard edition.
As long as encryption concern there was no built in option available in Microsoft SQL Server 2000, you have to have third party tool and expertise to use that tool while in Microsoft SQL Server 2005 it comes up with inbuilt encryption support.
Apart from that, MS SQL Server 2005 comes up with BI reporting tool as known as “YUKON”.
Reference: Microsoft Site
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah