2

In MySQL, I'm working with a database that has integrity constraints on foreign keys (nothing overly special here).

Is there a method to somehow visualize the effects of a cascading delete or update?

I would like to somehow see all records that will be effected, the whole chain of it, across all involved tables, preferably (but not a hard requirement at all) before they happen. Is this possible?

Update
I'm trying to estimate the impact of cascading deletes/updates, seeing the result would greatly speed up my understanding.

  • Can't you write a select statement(s) that would show the affect? – Erik Mar 17 '16 at 21:05
  • I can manually write such a query manually, but I would like the db engine to figure it out for me. To do it manually, currently peer over a 6 by 3 feet printout of the ERD and, as such, the manual method is quite prone to (human) error. – Jacco Mar 17 '16 at 21:08
  • Cascading deletes have always scared the bejeezes out of me because things can go horribly wrong in a hurry. I agree that a tool would be better in general, but it feels like this would only be useful for periodic maintenance needs, not for normal use. If that is the case I'd avoid the cascades, and test the hell out of my manual script. With proper use of transactions and rollbacks hopefully the foreign key constraints would alert you to problems with your script. Personally I trust the database more than me manually combing through data that is going to be stale after the next CRUD operation – Erik Mar 17 '16 at 21:18
  • In this case I'm trying to estimate the impact of cascading deletes/updates, seeing the result would greatly speed up my understanding. – Jacco Mar 17 '16 at 21:25
  • Unfortunately I don't know enough MySQL to write the script for you but there should be some sort of meta data available for your tables that you can use to build some dynamic sql recursively. That would allow you to see the effect of a cascading delete. I might be a wimp but I would avoid cascading deletes like the plague as I said above. It can be a nice feature, but it also makes it easy to shoot yourself in the foot. – Erik Mar 17 '16 at 21:33

Your Answer

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

Browse other questions tagged or ask your own question.