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
  • 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, 2015 at 12:55

1 Answer 1

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/

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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