1

I'm about to create a parent and child table. In planning how to do CRUD operations on this data, I was wondering what is better design when it comes to deleting data.

I'm using SQLite. I know that with many databases, you can set up cascading deletes. Assuming I set up the relationships properly to support this type of feature, my question is as follows:

Is it better to use cascading delete feature when deleting parent records, or could I stick to using transactions, and manually delete from the parent/child tables myself, with rollbacks when something fails?

The front end is a web application. Any suggestions / articles you can point me to would be great.

  • 1
    That depends on your business case, whether it's okay for you to silently delete child records whenever the parent is deleted, or do you wish to be forced to manually delete the child records first and then the parent record after that. – zgguy Jun 11 '15 at 12:55
1

This depends on the needs of the data and is also a design preference. Having CASCADE ON DELETE can be a cleaner implementation and better describe the relationship of the data.

The research that this article presents shows that CASCADE ON DELETE actually has a slight performance decrease over a straight delete in SQLite. But unless you have a consistently high load, it shouldn't be an issue.

Database    Speedup    CPU decrease
MySQL         45%        25%
Oracle        28%        11%
PostgreSQL    56%        55%
SQL Server    17%        22%
SQLite        -11%      -75%

http://www.codesynthesis.com/~boris/blog/2012/04/12/explicit-sql-delete-vs-on-delete-cascade/

  • If I could, I'd give more than +1 for the sentence "Having CASCADE ON DELETE can ... better describe the relationship of the data." – Colin 't Hart Jun 11 '15 at 13:06
  • The benchmarks are very interesting. Thanks for the clear answer and the link – dot Jun 11 '15 at 13:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.