March 6, 2014 at 9:57 am
Hi
I Understand, when you delete a row in the parent table, the database server also deletes any rows associated with that row (foreign keys) in a child table.
So I wanted to discuss, Is it a bad RDBMS practice, If we use on cascade delete option?
Scenario
Say a dot net front end app couldn't delete a rec because it is referenced by a foreign key constraint. DBA fixed that issue by adding on cascade delete option to that Foreign Key constraint.
Regards
Raam
March 6, 2014 at 10:18 am
$Raam (3/6/2014)
HiI Understand, when you delete a row in the parent table, the database server also deletes any rows associated with that row (foreign keys) in a child table.
So I wanted to discuss, Is it a bad RDBMS practice, If we use on cascade delete option?
Scenario
Say a dot net front end app couldn't delete a rec because it is referenced by a foreign key constraint. DBA fixed that issue by adding on cascade delete option to that Foreign Key constraint.
Regards
Raam
It is not necessarily bad practice but it is also not used extensively because it can introduce some serious performance issues. If you have a lot of cascaded deletes or deleting a lot of rows it can bloat the logs which can cripple your database. It it probably a better idea to use a delete stored proc which will delete the children first and then the parent. This type of approach can ease the pressure on the transaction logs.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 7, 2014 at 10:05 am
$Raam (3/6/2014)
HiI Understand, when you delete a row in the parent table, the database server also deletes any rows associated with that row (foreign keys) in a child table.
So I wanted to discuss, Is it a bad RDBMS practice, If we use on cascade delete option?
Scenario
Say a dot net front end app couldn't delete a rec because it is referenced by a foreign key constraint. DBA fixed that issue by adding on cascade delete option to that Foreign Key constraint.
Regards
Raam
I agree with Lange that ON DELETE CASCADE can introduce performance issues.
Moreover, it also obfuscates application functionality, and increases development costs because of the increased debugging that comes with it ("why did these rows suddenly disappear?"). I've seen it firsthand. It is far better to have a delete SP that explicitly deletes child rows and then the delete candidate rows.
Thanks
John
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply