To Cascade or not To Cascade...that is the question

Question

To Cascade or not To Cascade...that is the question

Jorge.G

SSC Enthusiast

Points: 192
More actions
April 24, 2008 at 11:29 am

#380152

Hi Everyone,
I been searching on Cascade Delete on tables...some how I still can not figure out if is a good practice to leave the db engine to handle the deletion by setting the relation and Cascade delete ...or to handle by code.
My question is:
If is better to write your own code (as some people suggest) , what is the purpose and why it is included as an option in the RDBS ?
When should I use it / Not use it?
Thank you in advance
Jorge

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

I assume you mean using the cascading delete vs. using some combination of triggers to automatically delete child records associated with a parent record.

I would use the built-in tools if I were to use a cascading delete. Coding it manually will result in more code - ensuring more maintenance.

I don't believe cascading deletes are a good idea at all. The point of referential integrity is to ensure you do not get orphaned records, but I also like the added benefit of the extra check to ensure I am not deleting a parent record that I still may need.

I think it is better to have your application delete records in the correct order.

Jorge.G SSC Enthusiast Points: 192 More actions · Answer 2

Thanks Michael,

What I was trying to say "Deleting by code" is handled by the application code.

Some one suggest that the records deleted by the cascade delete are not logged in the transaction log...Is that true?, if it is, then it will be one point against to have them in place...

Thanks

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 3

Michael Earl-395764

SSC Guru

Points: 53873

April 24, 2008 at 12:52 pm

#806835

That's not true, cascaded deletes are logged.

GSquared SSC Guru Points: 260824 More actions · Answer 4

I dislike using automatic cascading deletes.

First, they have problems with tables with more than 1 foreign key in them. For example, you can't put a cascading delete on a many-to-many table without getting error messages about recursion problems.

Second, they don't give me as much control over deletions as I like. I'd rather have an error saying, "you can't delete this customer, he has orders!", than have a live customer and all his orders be removed from the database on accident, when some user thought he was deleting a duplicate copy of the customer. (There are other ways to prevent this kind of thing, but not having cascading deletes is sort of a last line of defense in my opinion.)

Yeah, they work. But I see them as a lazy way to avoid having to write the deletion code correctly.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 5

I agree.

This is one of the features that feels like it does nothing but help facilitate having a poorly designed application. It's like not having foreign keys because you have an application that inserts records in the wrong order.

Now, there are times when you do not have control over the application so you have to do things like this, but if you are in the design phase of an application it seems like making sure the application deletes in the correct order is the best way to go.

...and now I will get off my soapbox.

Jorge.G SSC Enthusiast Points: 192 More actions · Answer 6

Thanks Michael,

I do not think that having them in place is a symptom of bad programming..., I am not saying that I am inclined to them either...

One of the points that puzzles me is what are the valid use of them...it seems to me that is a consistent -long term solution- and across the time - way to ensure that the db will not have orphaned records...

I see your point on doing that manually, but what I am concerning is the maintenance part... the person that is doing the maintenance will need to spend a lot of time training on what should be deleted from what tables...when that can be done for free in the back end always in a consistent way, no matter if the programmer forgot to delete a record from a table or not...

I appreciate a lot your comments and suggestions.

Thanks again.

GSquared SSC Guru Points: 260824 More actions · Answer 7

Yes, they do have that advantage. It is easier.

But, since a missing delete will raise an error if you have your foriegn keys set up correctly, adequate testing should find that out.

It does have advantages. I personally don't think they outweigh the disadvantages. But it really isn't that important. Go with whichever seems best to you.

This is, in my opinion, one of the minor details that's more based on gut instinct and opinion, as opposed to serious analysis and major differences.

Though, as mentioned before, there are places where you simply can't use cascading deletes. Since I like consistency, I use the same method in all places, which means include it in the proc.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Jorge.G SSC Enthusiast Points: 192 More actions · Answer 8

Hi GSquared,

Thank you very much for your comments, they are very valuable.

I have a better picture now.

Thank you guys!

Jorge