Cascading delete in SQL Server

Sheonarayan
Posted by in Sql Server category on for Intermediate level | Points: 250 | Views : 12355 red flag
Rating: 5 out of 5  
 1 vote(s)

In this article, we shall learn how to enable cascading delete option while creating foreign keys constraints in the SQL Server database.

Introduction

Referential Integrity is very very important thing to consider while designing the database. Cascading delete option while creating foreign key helps us overcome the chances of orphan records into the database while delete records.

Lets assume a scenario, where we have a Doctors table containing all the Doctor related details and a Patients table containing patient record with doctor referenced into it to know which patient belongs to which doctor. Ideally, when a doctor record is deleted from the Doctors table, all the patient for that doctor should also get deleted but that would not happen automatically unless we go and physically delete Patients table data for that Doctor. To automatize this process, we can use the cascading delete option of SQL Server while creating a foreign key relationship. 

How to enable Cascading delete in SQL Server


To demonstrate cascading delete, we are going to take example of two tables, Doctors and Patients and below is their structure. Here we are going to learn through Microsoft SQL Server Management Studio, however it can be done through code as well.



Here DoctorId of Doctors table is the auto increment primary key field and DoctorId of Patients table is the foreign key. To configure this, open the database table in design mode, right click on any one of the field and select Relationships... as shown in the picture below.



You will get following dialogue box (blank dialogue box).



Now click on the Add button at bottom left and select click on "Tables And Columns Specific" rows, a ... box appears, clicking it asks for Primary key table and Foreign key table and their respective fields.


Select as per requirement (as shown above) and click OK. Now scroll the previous dialogue box down and explore "Insert And Update Specific" row and we will see Delete Rule, here we need to opt for Cascade value.



Click Close button and finally click on Save icon from the SQL Server Management Studio.

Now the Cascading delete of Doctors and Patients table is configured. Here Primary table is Doctors and Foreign table is Patients so when a record from the Doctors table would be deleted the corresponding record from Patients table (Record with that DoctorId in the Patients table) would automatically gets deleted to ensure that there is no orphan records in the Patients table. 

This happens either the Doctors table record is deleted directly in the database table or through any application.

Hope this article was useful, do share your comment or feedback.

Thanks for reading and do share this article to your friends and colleagues.

Recommendation
Read Implementing Custom Paging in ASP.NET with SQL Server after this article.
Page copy protected against web site content infringement by Copyscape

About the Author

Sheonarayan
Full Name: Sheo Narayan
Member Level: HonoraryPlatinum
Member Status: Administrator
Member Since: 7/8/2008 6:32:14 PM
Country: India
Regards, Sheo Narayan http://www.dotnetfunda.com

Ex-Microsoft MVP, Author, Writer, Mentor & architecting applications since year 2001. Connect me on http://www.facebook.com/sheo.narayan | https://twitter.com/sheonarayan | http://www.linkedin.com/in/sheonarayan

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)