Will Cascade Delete help Me?

Question

Will Cascade Delete help Me?

IN_Sandeep

SSCertifiable

Points: 6259
More actions
January 25, 2008 at 5:19 am

#178562

Hi All,
In My database, I have a parent table (ParentTable), which is having more than 20 numbers child table (Child - 1, Child - 2,...etc) by using primary and foreign key relationship.
My question here is :
Should I use a stored procedure to delete all the respective child data as per delete key data (PID) from parent table
OR
Should I use a cascade delete concept to delete PID data from the child tables when some one delete PID data from parent table?
Ex:
=====
ParentTable.PID (Pk) --> Child - 1. PID (FK)
ParentTable.PID (Pk) --> Child - 2. PID (FK)
....
like this.
Here I can use the cascade delete concept,
then my question is:
Will it be a problem for me in the time of deployment in the
web server or not?
Last time i had a issue with schema binding.
please help me on this...
Cheers!
Sandy.
--

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

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

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 1

My two cents, use the stored procedure. Cascading deletes is extremely unforgiving. That can be good, but it can be bad. As long as you've got appropriate constraints in place, you can't accidently delete a parent and no children. Using a stored procedure gives you very precise control so that you can run the deletes in the same order as you run the inserts which will help to avoid deadlocks. I really just prefer the direct, hands on control that doing the deletes through procedures gives you.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

IN_Sandeep SSCertifiable Points: 6259 More actions · Answer 2

Thanks Grant,

I really feeling great that i am a part of Sql Server Central.

I will change the DB Design in the Monday.

I really appreciate the way you just guide me...

I am leaving for the day now...!!!

Thanks for the same again...take care...

Cheers!

Sandy.

--

Bradley Deem SSCrazy Points: 2565 More actions · Answer 3

I use cascade deletes quite often (when ever appropriate), but as Grant Fritchey says use them with care.

For instance, consider the following (the naming convention here is just for clarity)

tblCustomers,

tblSalesOrders

tblSalesOrderLines

tblInvoice

I would put cascade delete on the Sales Order to the Sales Order lines relation as it would save me the trouble of maintaining any stored proc. It just easier happens behind the scenes without worry or trouble. I think it's great!

Now, there would be a relationship between SalesOrders and Customers, and chances are if a customer is related to a Sales Order they might also be related to Invoices. Obviously, deleting Invoices would get you in hot water with Accounting. So there would not be a cascade delete on the Customers to Sales Order relation and there would NOT be a cascade delete on the Sales Order to Invoice. However, I would put one on the Invoice to Invoice Lines (again easier/don't have to think/worry about it).

IN_Sandeep SSCertifiable Points: 6259 More actions · Answer 4

Hey Brad,

I am now using more than 15 numbers of cascade delete in my Database.

Is it correct to use cascade delete in all relation?

the reason behind using it,

its easy for me to delete the data from main table.

not depend on all the child table.

otherwise i have to check more than 15 numbers of table to delete one record from Main table manually.

I prefer Grant's way because, personally I feel it should be handled by the fronted web application by manually calling the stored procedure and delete the data from the child table and then Main table.

Please correct me, If I am wrong.

and please give me the correct solution, so I can implement it in my Database.

Cheers!

Sandy.

--

Ian Yates SSCoach Points: 19738 More actions · Answer 5

There's no "right" answer. It's always "it's depends" 😀

In your system it may make more sense to have a deleted bit flag in your (for example) orders table rather than just deleting the order as often the presence of an order indicates that you may have done things with that order such as updating a general ledger, placing orders on suppliers, etc.

If you have a simple bit flag then your app code can be aware of it so you get the best of both worlds

1. You need just set one deleted flag on the (for example) order table so there's no need for a stored proc to delete from all of the child tables. You get the convenience of cascading updates in terms of code required..

2. The data remains in your DB

But, as always, it depends 😀

IN_Sandeep SSCertifiable Points: 6259 More actions · Answer 6

Hey Ian Yates,

I agree with you, but in my case, no need to keep the old data in production database, so i wants the data should be remove permanently.

So what should i do?

Cheers!

Sandy.

--

Bradley Deem SSCrazy Points: 2565 More actions · Answer 7

One other solution, not yet mentioned, is using Instead of triggers. You could use an Instead of Delete trigger to move the records that are to be deleted to another table thus avoiding a Deleted bit flag and the maintenance that goes with it.

To Sandy:

I cannot say whether the cascade deletes you have in your database are right or wrong. In my above scenario with Customers, Sales Orders, Invoices if I implemented cascade deletes that wouldn't necessarily be wrong. I just put stops on them because I don't want someone to delete a customer that would affect dependent static data.

This is where I think breaks in cascade deletes should be made. Accounting depends on Invoices to remain static thus don't allow them to be deleted, therefore no cascade delete from the Sales Order to the Invoice.

Granted the Sales Order total depends on Sales Order lines so in that situation an Instead of Delete trigger could be used to prevent deleting Lines when a Sales Order has been made into an Invoice. Of course now you are putting business level logic into your Data Layer which some may object to. I however do have logic like this in my layer as it is very unlikely to change and such a rule should be enforced. Keep in mind you could go batty putting in all of those minutiae rules, it's a cost/value return so often times I'd just slap a cascade delete on it and call it good. Then restrict when a user can delete a sales order at the UI level since you have to do that anyways.

So in brief. Don't allow a cascade delete to take out dependent data that should remain static either because of a business rule or a data integrity rule.

IN_Sandeep SSCertifiable Points: 6259 More actions · Answer 8

Hi Brad,

Thank You,

I will proceed my work as per you and Grant Specified initially.

If any needs, I will get back to this topic.

Cheers!

Sandy.

--