The topic of a cascade delete came up on a project the other day and I was surprised to find one of the project members wasn’t familiar with the term or the concept. I thought I would share a short lesson and reflect on testing associated with cascade deletes.
A cascade delete is when an on object is deleted in an application and on the backend in a relational database, the delete cascades through the associated tables in the database removing associated data. (And hopefully both removing the appropriate information and leaving the other data.) The concept isn’t difficult to imagine although there are ripples of what takes place, what can go wrong, what could be tested, and how a failed delete can be found.
Being a fan of examples as a way to define and describe, here’s an example.
Imagine an ecommerce website that sells books – suppose a book is no longer being offered for sale on the site. An administrator of the site might delete the book.
On the delete transaction, the entry for the book would be removed from the book table. Likely the delete has been coded such that when a book is removed from the book table, a trigger deletes the information on the other associated tables (such as a pricing and publisher table). The delete effectively cascades.
If the trigger has not been coded or isn’t working, the result could be orphaned data. Or the delete transaction could fail leaving the book in the database.
I guess this might be categorized as database testing. I suppose. But orphaned data can have an impact on performance when a sufficiently large amount of data has been orphaned and the performance of a site is comprised.
Here’s another way to stumble into a failed cascade delete. Imagine the publisher information associated for the book should have been removed but not all the information for the publisher. After all there could be many other books offered by the publisher. If I look up another book from the publisher and find the publisher information is missing, I might find a page not found or error of another type. A failed cascade delete could be the culprit behind missing data.
Another variation might be the bookseller no longer carrying any books from a particular publisher. Now the delete becomes a delete of a publisher record and all associated books being removed.
I may just be a data geek, but I don’t consider this database testing as much as I consider this as having an awareness of how deletes and for that matter inserts and updates are handled by the application. I like to know how data flows in and through an application.
If I have to wait to back into bugs through the front end of an application then it might take a long while to find them. When data issues from production get reported, a failed cascade delete is a possibility that comes to mind. It might be why a bug isn’t so easily or obviously reproducible – the specific data is always a potential factor in a bug. This is one idea I have in my collection of ideas about how to find and/or reproduce a bug, the ripple effects of a cascade delete.