AlphaPress Publishing
P.O. Box 5942 Albany, NY 12205
Tel: 518-250-3890
Email: alphapress@hotmail.com  


  
Click to buy this book from Amazon

Section Menu 

News

Pindaro is now working on a comprehensive book on SQL Server 2014.  For any comments you can email the author at alphapress@hotmail.com.

  

 

 

What are cascade deletes, how to use them, and what they mean

To contact the author click here

This article is from the book "Access 2007 Pure SQL

To download the sample database click here


What are cascade deletes, how to use them, and what they mean

In order to understand cascade deletes in full, let’s work with the record of a customer from five years ago who is no longer in business.  We want to delete this customer from the database so that it does not come up in queries and does not take up space.  

We could simply go to the customers table and try to delete this record.  However, if there are associated orders with this customer, the database will not allow us to delete it since we would then end up with orphaned records in the orders table.  Referential integrity rules do not allow this to happen, and we would not be able to delete the customer.  (For a full explanation of referential integrity, please check chapter 3.  Understanding referential integrity is a must for all database professionals and users).  To delete this customer manually, we should first go to the orders table and delete all of the orders associated with this customer.  However, since we also have a many-to-many relationship between Orders and Products, we first need to go to the Products_Orders table and delete the associations (records) of Orders and Products for that customer.  If we need to delete the customer Mary from the database, we need to do the following in the order provided:

         Delete from the Products_Orders table all of the records with orderid 353, 538, 609, and 982.  For example, order 353 contains products 1, 2, 11, 20, 27, 31, and 51.

         Delete from the Orders table the orders with orderid = 353, 538, 609, and 982 since they all belong to Mary.

         Finally, delete Mary’s record with CustomerID = 2 from the Customer table.

Even in this simple scenario, deleting a customer is an involved process.  Imagine the scenario where you have hundreds of customers with thousands of associated orders.  It would be humanly impossible to remove customers manually. 

This is where cascade deletes come in.  By using cascade deletes, we can delete a customer in the primary table, and the database itself will delete all references to that customer in all related tables.  In this example, if cascade deletes are on, when we delete Mary from the customers table, the database will automatically delete all of Mary’s references in the Orders and Product_Orders tables reliably and at once.  To turn cascade deletes on, follow these steps: 

1.       Click on the "Database Tools" tab.  Then, in the group "Show/Hide", click on "Relationships".

2.       This will open up the Relationships window as shown below:

3.  Double click on the relationship line between customers and orders.  The following screen will appear.  Click on "Cascade Delete Related Fields", and click "OK".

4.    Repeat step two for the relationship between the tables Orders and Products_Orders, and enable cascade deletes as well.

5.  At this point, if we open the customers table and delete Mary’s record, all of Mary’s orders will be deleted from the Orders table, and all respective associations between Orders and products will be deleted from the Products_Orders table.