Who uses ON DELETE CASCADE in a PHP application?

[NotionCommotion]

This is more of a PHP question (or at least an application question) and not really a SQL question, so please read on.  First, would like some opinions of my assumptions...

 

ON UPDATE CASCADE should typically be used on natural keys but never on surrogate key regardless of whether a human or PHP is executes the queries as the natural key values may be changed in the future.

 

ON DELETE CASCADE should typically be used on surrogate keys where a human executes queries, but most likely never on natural keys regardless of whether a human or PHP executes the query as it just seems wrong.

 

Disagree?  If so, why?

 

So, my question....

 

Should ON DELETE CASCADE (or maybe even ON DELETE SET NULL for that matter) ever be used when PHP solely executes the queries?  Please provide rational why or why not.

 

Thank you

 

[requinix]

ON UPDATE CASCADE should typically be used on natural keys but never on surrogate key regardless of whether a human or PHP is executes the queries as the natural key values may be changed in the future.

 

ON DELETE CASCADE should typically be used on surrogate keys where a human executes queries, but most likely never on natural keys regardless of whether a human or PHP executes the query as it just seems wrong.

 

Disagree?  If so, why?

I disagree about the "this is more of a PHP question and not really a SQL question" part.

 

So, my question....

 

Should ON DELETE CASCADE (or maybe even ON DELETE SET NULL for that matter) ever be used when PHP solely executes the queries?  Please provide rational why or why not.

That's more like it.

 

It doesn't matter who executes a query. What matters is who you entrust to maintain data integrity: do you want your application's code to do all the work, or do you want your database to do all the work? Programmers typically prefer the former, DBAs typically prefer the latter. Both have advantages and disadvantages.

My preference: do it in code. It's highly visible, easy to change, and with proper modelling needs only be implemented in one place in code.

[Jacques1]

The question is: What do you want to achieve? You've stated a lot of technical rules about natural keys vs. surrogate keys, manual queries vs. application queries etc., but I'd concentrate more on the underlying goal.

 

If you don't define any referential action, the default is RESTRICT, which means MySQL will reject the UPDATE or DELETE query altogether. This is useful if updating or deleting a parent record should be considered an error. For example, you might want to force the application user to manually delete the child records before deleting the parent (for whatever reason).

 

Using ON UPDATE CASCADE generally makes a lot of sense, because it allows you to safely update a parent key. If you think that the key shouldn't be updated at all, you should prevent that rather than preventing the propagation of the change. In other words: Make sure an UPDATE isn't possible in the first place.

 

The referential action of DELETE queries, again, depends on your goals:

• Are you afraid of losing records? Then you should probably prevent DELETE queries altogether and set a “deleted” flag instead. For extra safety, you might use an explicit(!) RESTRICT action.
• Do you want to keep the child records after the parent has been deleted? Then you'd use SET NULL (assuming this is possible for the child records).
• Or do you want to cleanly remove all data that depends on the parent record? Then use CASCADE.

So it's not about the type of the key or the origin of the query. It's about the data.

[NotionCommotion]

I disagree about the "this is more of a PHP question and not really a SQL question" part.

 

That's more like it.

 

It doesn't matter who executes a query. What matters is who you entrust to maintain data integrity: do you want your application's code to do all the work, or do you want your database to do all the work? Programmers typically prefer the former, DBAs typically prefer the latter. Both have advantages and disadvantages.

My preference: do it in code. It's highly visible, easy to change, and with proper modelling needs only be implemented in one place in code.

Yea, my first two statements with an "Agree?" question were more of a SQL question.  Added them trying to give context, but probably shouldn't have.

 

But the last part was where I need help.

 

You imply that not using ON DELETE CASCADE results in highly visible, easy to change, one place in code.  Presumably, using ON DELETE CASCADE may result in the opposite?  Not trying to go round and round in a debate; just trying to better cement my own approach.

 

I believe that it does matter who executes a query.  If I have multiple applications (or even more relevant, multiple people) performing queries, then ON DELETE CASCADE likely makes sense.

 

Do you know of any other reasons why one would recommend using ON DELETE CASCADE?

[NotionCommotion]

The question is: What do you want to achieve?...

 

Or do you want to cleanly remove all data that depends on the parent record? Then use CASCADE.

 

I wish to remove all the data that depends on the parent record.  So, do you use CASCADE for this need, or do you use RESTRICT or NO ACTION on the dependent records, use the application first delete the dependent records, and then have the application delete the parent records?  Why?

[NotionCommotion]

Or maybe instead of basing the decision to use ON DELETE CASCADE on the trust of the user who makes the change, it should also be based on the type of record deleted.

 

For instance, is a many-to-many cross table a good canidate to always use it on?

[Jacques1]

When you want to delete all descendants, then ON DELETE CASCADE is perfect. I see absolutely no reason why one would prefer to do this manually. This is especially true for complex relations that would require you to write some big recursive function to traverse the dependency tree.

 

So, yes, I would use (and have used) ON DELETE CASCADE for this purpose.

Edited November 20, 2015 by Jacques1

[NotionCommotion]

When you want to delete all descendants, then ON DELETE CASCADE is perfect. I see absolutely no reason why one would prefer to do this manually. This is especially true for complex relations that would require you to write some big recursive function to traverse the dependency tree.

 

So, yes, I would use (and have used) ON DELETE CASCADE for this purpose.

 

Not worried about ON DELETE CASCADE not being perfect, only my memory not being perfect.  Typically I use RESTRICT 90% of the time and SET NULL the rest of the time.  I will sometimes attempt to delete a record and catch it and respond if it fails an integrity constraint.  Not good I if forgot that I have it to cascade!  I also have a problem with viewing some script, and not seeing the other functionality such as CASCADE’s or triggers (not saying I never use triggers, I just don’t make a habit of doing so).  I expect better documentation by me may mitigate both of these concerns.

[ignace]

Your application should only be database aware, not be the database. Define your database with proper referential integrity, events, views, and procedures as the database is optimised for this type of task.

 

Your application should only know it's interface like every other REST API out there. If you use a (powerful) ORM you'll be able to translate these procedures and views to entities using a ResultSetMapper. Just like you would map an entity from a request when interfacing with a REST API.

 

Now that you know this on to your question. ON DELETE [CASCADE|RESTRICT|SET NULL] depends on how the relations between your rows exists:

 

ON DELETE CASCADE:

You use this when it makes no sense to keep the child row if the parent row is removed. For example an OrderLine and an Order. If you don't have the Order, then having the OrderLine's makes no sense.

 

ON DELETE RESTRICT:

Like was already explained it simply prohibits you to delete a parent if it has childs. Just like you can't delete a directory if it contains files. You need to move or delete the dependent rows first in order to remove the parent. This is the default behaviour and you'll get a "Cannot add or update a child row: a foreign key contraint fails" if you do.

 

ON DELETE SET NULL:

This is useful for optional relationships. For example between a Customer and a Cart. If the Customer currently has a Cart the field contains a FK to the Cart.Id. If the Customer on the other hand does not have a Cart this is simply NULL and a Cart can be assigned. The Customer can exist regardless if he has a Cart or not.

 

In order to impose theses restrictions you need to know how the relationships are between your data.

Edited November 21, 2015 by ignace