sql server 2008 - What are the Pros and Cons of Cascading delete and updates?

Question

Maybe this is sort of a naive question...but I think that we should always have cascading deletes and updates. But I wanted to know are there problems with it and when should we should not do it? I really can't think of a case right now where you would not want to do an cascade delete but I am sure there is one...but what about updates should they be done always?

So can anyone please list out the pros and cons of cascading deletes and updates ? Thanks.

Answer 1

Pros:

• When you delete a row from the Parent table all the foreign key rows are deleted
• This is usually faster than implementing this with triggers
• Orphaned rows are unlikely

Cons

• Orphans are possible
• If by mistake you delete a row in the parent table all the rows in the corresponding child tables will be deleted and it will be PITA to figure out what you deleted