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.