Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Dilemma: Cascade delete or Join delete

It's not a specific question, more a general wondering.

When you have to make a delete on multiple tables in a 1:M relationship, is it better to make a FK constraint with a cascade delete or join the tables in the delete statement.

I had an old project that had separate delete statements for related tables, and a few times some of the statements were not executed and data integrity was compromised. I had to make a decision between the two, so I was thinking a bit what would be a better solution.

There is also an option to make a stored procedure or a transaction.

So I am looking for an opinion or advice...?

like image 384
Martin Taleski Avatar asked Oct 19 '11 12:10

Martin Taleski


People also ask

Is it good to use on delete cascade?

Cascading deletes should not cause unexpected loss of data. If a delete requires related records to be deleted, and the user needs to know that those records are going to go away, then cascading deletes should not be used.

When to use cascading delete?

Use the ON DELETE CASCADE option to specify whether you want rows deleted in a child table when corresponding rows are deleted in the parent table. If you do not specify cascading deletes, the default behavior of the database server prevents you from deleting data in a table if other tables reference it.

Is cascade a delete rule?

CASCADE. It is used in conjunction with ON DELETE or ON UPDATE. It means that the child data is either deleted or updated when the parent data is deleted or updated.

What is the purpose on delete cascade deletes the dependent?

ON DELETE CASCADE clause in MySQL is used to automatically remove the matching records from the child table when we delete the rows from the parent table. It is a kind of referential action related to the foreign key.


2 Answers

I'd say it's safer to use a cascade delete. If you decide to use joins, you have to remember to use them every time you delete anything from parent table; and even if you're disciplined enough to do that, you can't be sure about your coworkers or people who will support your software in the future. Also, encoding such knowledge about table relationships more than once violates DRY principle.

If you use a cascade delete though, nobody has to remember anything, and child rows will always be deleted as needed.

like image 144
socha23 Avatar answered Sep 25 '22 00:09

socha23


If your database has proper RI defined for it then there shouldn't be any case of compromised data integrity. All of your related tables should have declarative RI, which means that you can't delete a parent while it still has children.

Also, if you have code that is only deleting some of the rows at times then that is poor coding and poor testing. These kinds of actions should be a single transaction. Your suggestion of using a stored procedure is a great approach for solving that problem and is pretty standard.

As has already been mentioned, cascading triggers have the danger of deleting rows that someone did not intend to delete. Consider that sometimes people might be accessing your data from somewhere outside of your application, especially when fixing data issues. When someone accidentally tries to delete the wrong parent and gets an RI error that's good. When they accidentally try to delete the wrong parent and it not only deletes that parent but 20 children in 5 other tables, that's bad.

Also, cascading deletes are very hidden. If a developer is coding a delete for the parent then they should know that they have to use the delete stored procedure to take care of children. It's much preferable to have a developer not code against that, get an error, and fix his code (or realize that he doesn't really want to do all of that deleting) than it is to have a developer throw in a delete and have no one realize that it's killing off children until the code has gone live.

IMO, I prefer to have my developers knowledgeable about the application rather than make it easier for them to remain ignorant of it.

like image 28
Tom H Avatar answered Sep 26 '22 00:09

Tom H