Setting up Cascading Deletes in SQL Server
When setting up CRUD functionality with linked tables in a DB by default SQL Server will not let you delete a parent record if there are child records that refer to it. This would cause an orphaned record and is prevented by Sql Server. However, there is a way to modify this feature if you desire to have all child records removed with the parent record. For example, if we have a database with items linked to categories as below.
The data in the tables shows that I have items listed that are linked to specific categories
If we try to delete the category CDs from the database without first deleting all the child records we are presented with the following error message.
To change this behavior and have SQL Server remove the parent and all child records in one action we go into the database diagram, right click on the relationship line between the two tables and select the properties option.
In the properties for the relationship we change the delete action to “Cascade”.
This will remove all child records from the Items table when an associated parent record from the Categories table is deleted.
Note that is will effect relationships clear down to the end of the tree, or prevent any delete if this is not setup on every relationship. For example, lets take a database that has companies linked to customers, customers linked to orders, and orders linked to orderDetails. If I have cascade set up between companies, customers and orders then try to delete a company but there are orderDetails linked to an order which is linked to a customer that is linked to that company the entire delete operation fails and nothing is removed. However, if I have cascade delete set up on all relationships and remove a company every customer, order and orderDetail associated with that company will also be removed. This can be a very beneficial but also a very dangerous thing. Remember, with great power comes great responsibility!