How to test a delete operation?

Question to the community: How can you best test a delete operation?

For e.g. When you test the following API – DeleteObjectFoo(“ABCD”) – what should we test to verify that this function actual deleted “ABCD”? This API is just a placeholder for an API that deletes something. The question is that, in general, how do we verify that “something” was indeed deleted?

Should we simply test that “ABCD” is deleted from the database (or whatever the underlying storage is)? That is typically what is done. The question then becomes how do we catch a bug which not only deletes “ABCD” but also “XYZ”???

A colleague some time back had raised this issue since we ran into exactly this sort of a bug and found to our dismay that the existing tests were not catching this bug.

What do we change to test for a delete operation in general so that the testing becomes foolproof – or can it even be made foolproof?

Comments (8)

  1. Steve Wolf says:

    To validate your delete (1) deleted the item you requested and (2) did not delete any other item in the set you may have to take a snapshot of at least the primary keys (if you are deleting by primary key) before and after the delete request.  Then iterate through the two sets to look for differences.  If there is only one difference – the item which you requested to delete does not exist in the ‘after’ snapshot – you are good.

    This would not take into account any ‘side-effects’ to the record delete such as cascade delete relationships or actions take by a delete trigger which affected other related tables.

  2. nihitk says:

    Hi Steve – that’s a good suggestion – look only at the primary key values and go from there. Quite efficient.

    But what about the cascading delete relationships that you mention? In my opinion that is quite a common scenario in complex applications. How can we efficiently account for it? What about the triggers – that gets even more complicated. Any way we can handle that?

  3. Colin Bowern says:

    When you get into complex deletes you are building a more complex unit test which I usually refer to as a scenario test (that is, it is testing and asserting beyond a simple operation).  Assuming we’re working with the PK on the database the basic unit test should delete ABCD by counting the records in the table, removing the instance by the call, checking the count again, and evaluating the difference to be one.

    If you are going to test cascading relationships then you’ll have a more complex test that will need to understand (or automatically traverse) your relationships and triggers.  A way to model this without a whole lot of complexity is to use a before and after dataset covering the affected tables, produce a change set between the two, and inspect that for the expected number and types of changes.  See Dino’s article from 2003 on getting dataset changes –

  4. nihitk says:

    Hi Colin,

    That is a good suggestion to use before and after datasets and diff the two. In theory this should be usable though for a complex set of tables (for e.g. the PurchaseOrder and all the 10+ tables which get populated with every save) inspecting the set of changes and verifying they are valid will likely get very complex (seems like it might become one of the cases of the test code getting more complex than the product code).

    Actually I just thought about it some more and realized that even the Primary Key might also be oversimplying in many cases. For e.g. imagine deleting all employees with the FirstName = ‘Joe’ and LastName = ‘Smith’ from an employee database. What all do we need to do to make sure that not ALL ‘Joe’s or ‘Smith’s in the database are deleted instead of just the ones that fulfill both these requirements?

    Everything is simple if you can create data on the fly just for the test, but then the issue is that it won’t be real-world – you need some other noise data to be present as well.

    I am just thinking out aloud here – seems quite interesting.


  5. mallik.webaroo says:

    One way is, lets assume each delete operation deletes X tuples (We can always calculate it) after the operation we need to see that

    total_tuples_new=total_tuples_old- X

    and in case of cascade delete operations, say one operation deleting X and other Y. After the completion of both operation it should be

    total_tuples_new=total_tuples_old- X-Y+S Where S is common tuples of the two delete operations.

    Here there will also be cases of looking if any insertions being made. This strategy will also help in testing the Atomocity of delete operation.

    Hey Nihit, I am new to your blog and your blog is cool 🙂

  6. CM says:

    i want to design a database for my project, so please post me solution. thanks

  7. sanfar says:

    hey nithit,

    your blog is really good man.

    as of myself i am doing manual testing so far

    really wanted to get into automated testing and start using tools i know only java programming.

    in your recent posting u have been mentioning about the model based testing its quite interesting , i would really like to learn more about it and want to experience , i have aslo downloaded the tool for which u gave the reference,hopefully spec explorer works with c# or VB. tell what other tools i can use for model based testing.

  8. Weddings says:

    Question to the community: How can you best test a delete operation? For e.g. When you test the following API – DeleteObjectFoo("ABCD") – what should we test to verify that this function actual deleted "ABCD"? This API is just a placeholder