0

I have a parent table say A and a child say B and B references A's primary key so most people will use on delete cascade to modify the tables when they delete from the parent A. My question is: is there any example of a situation that I WILL NOT need 'on delete cascade'? When will it be not useful to use?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
CSawy
  • 904
  • 2
  • 14
  • 25
  • 2
    ecommerce system where you delete a category, which would delete all products in that category, and delete those products from any orders previously placed. "oops" – Marc B Nov 17 '12 at 04:05

2 Answers2

1

I don't like Marc B's example, because products generally would not be "children" of a category. Products and Categories can overlap, with a many-to-many relationship.

I have ON DELETE SET NULL in situations where the data is still useful without the parent.

E.g.

Suppose you have a translations table that contains columns id,translation_category,from_text, to_text

That table contains various text to text translations. The translation_category is a foreign key that references a specific field where the translations would be primarily used. But you could also do queries that ignore that key to get a count of common from_text and to_text values, since they may be repeated for different translation_category values.

That data is still perhaps useful even if you happen to delete one of the translation_category parent records. So I would use ON DELETE SET NULL there.

Of course, that same schema could be changed to put translation_category into a many-to-many linking table, but the same principles still apply.

Buttle Butkus
  • 9,206
  • 13
  • 79
  • 120
0

During table maintenance/recovery, the DBA may temporarily turn off delete cascade so the parent table can be emptied and reloaded for example without damaging/affecting data outside the table.

Bohemian
  • 412,405
  • 93
  • 575
  • 722