CODEWITHSUNDEEP
mysqlcascadecascading-deletes
CSawy
CSawy

Reputation: 924

When is on delete cascade unuseful?

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?

Upvotes: 0

Views: 96

Answers (2)

Buttle Butkus
Buttle Butkus

Reputation: 9476

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.

Upvotes: 1

Bohemian
Bohemian

Reputation: 425198

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.

Upvotes: 0

Related Questions