SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


CASCADE - 1


CASCADE - 1

Author
Message
marlon.seton
marlon.seton
SSC Eights!
SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)

Group: General Forum Members
Points: 903 Visits: 319
Lokesh Vij (3/20/2013)
db4breakfast (3/20/2013)
I never use cascade delete on a production db. It seem to be safer by allowing users mark order as "error" instead.


+1
same here. i feel it is dangerous to use on delete cascade in production...as a mistake in deleting a parent records will trigger deletes from all the child records w00t


We use triggers to delete the child records when a parent record is deleted. Is that generally considered a bad thing to do?
marlon.seton
marlon.seton
SSC Eights!
SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)

Group: General Forum Members
Points: 903 Visits: 319
This is a question about table design raised because of this QoD.

Re this part of the question:

"CREATE TABLE [dbo].[OrderDetail](
[OrderDetailID] [int] NOT NULL,
[OrderID] [int] NULL,
CONSTRAINT [PK_OrderDetail] PRIMARY KEY CLUSTERED
([OrderDetailID] ASC))"

why would you allow what is going to be the foreign key (OrderID) to be NULL in the table definition? I'm assuming you would not want an OrderDetail record without an Order record so why is OrderID not defined as NOT NULL in the definition of OrderDetail? Or does the setting up of the FK constraint mean it doesn't actually matter?
Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10850 Visits: 11971
Toreador (3/21/2013)
Using cascade delete. If the business rule says that is the appropriate behaviour, then why avoid using inbuilt functionality that achieves exactly what you need?

One possible reason is that you can't control the order of locks being taken with cascading deletes and cascading updates. That makes it harder to avoid deadlock scenarios if you are in a high concurrency scenario.

Other than that, I see no reason not to use this feature *IF* that is what the business wants. However, in my experience the number of scenarios where the business actually wants to delete dependent rows automatically is very small.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10850 Visits: 11971
marlon.seton (3/21/2013)
We use triggers to delete the child records when a parent record is deleted. Is that generally considered a bad thing to do?

Bad? I would not say so (but others will disagree with me).
But it's not smart either. If you have a business need for deleting child rows, why go to the lengths of building your own solution when the functionality you need is available out of the box?

Okay, if you run into the issue with multiple cascading paths, you have no choice but to roll your own. And if you get a lot of deadlocks because of cascading updates, using triggers might alleviate that a bit. But other than than, why bother?


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
marlon.seton
marlon.seton
SSC Eights!
SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)

Group: General Forum Members
Points: 903 Visits: 319
Hi Hugo,
Thanks for your reply.

By 'why bother' I guess you're meaning why bother using triggers instead of cascade delete? Because the 4GL in which we write the application can't handle FK constraints (and please don't ask why we use such a 4GL, that's a whole other bag of hurt).
Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10850 Visits: 11971
marlon.seton (3/21/2013)
This is a question about table design raised because of this QoD.

Re this part of the question:

"CREATE TABLE [dbo].[OrderDetail](
[OrderDetailID] [int] NOT NULL,
[OrderID] [int] NULL,
CONSTRAINT [PK_OrderDetail] PRIMARY KEY CLUSTERED
([OrderDetailID] ASC))"

why would you allow what is going to be the foreign key (OrderID) to be NULL in the table definition? I'm assuming you would not want an OrderDetail record without an Order record so why is OrderID not defined as NOT NULL in the definition of OrderDetail? Or does the setting up of the FK constraint mean it doesn't actually matter?


In this specific case (**), it does indeed make no sense. I can not think of any scenario where you would want an orderdetail that is not associated with an order.
But more in general, there definitely are situations where I would use a nullable foreign key. For instance in an Employee table, I might have a column AssignedToDept. Not all employees are assigned to a department, so for some this column is NULL. But for the employees that do have a value in this column, I want to make sure that this is a valid department, so I would add a foreign key constraint between this column and the Departments table.


(**) Many bad table designs come from mindlessly adding a surrogate key to each table. A proper database design method would first create an entire data model without any surrogate key. Only when ready to actually implement this logical data model into a physical database would surrogate keys be added to tables where this is warranted.
If that had been done for the use case of this question, the logical primary key of the OrderDetail table would have been a composite key, consisting of the combination of the primary key of the Order table and one extra column (probably LineNumber or DetailNumber). As the primary key, this would have been not nullable. Then, if at implementation time a surrogate key would have been considered appropriate for this table, the corresponding changes would have been made, but that would not have affected the nullability of this column.

And for full disclosure - the above is not a criticism of the question; I am aware that the question is a very simplified scenario targeted at testing a specific behaviour of foreign key constraints. But I do see mistakes like this in actual tables, and they never would have been made if the designer had not jumped the gun and started with a surrogate key on each and every table.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10850 Visits: 11971
marlon.seton (3/21/2013)
By 'why bother' I guess you're meaning why bother using triggers instead of cascade delete? Because the 4GL in which we write the application can't handle FK constraints (and please don't ask why we use such a 4GL, that's a whole other bag of hurt).

That's exactly what I meant. Wink
Ans you seem to have answerede the question yourself. I already gave two examples where specific SQL Server limitations make it impossible or undesirable to use cascading deletes, so that triggers are the next best option. You give a great example where a limitation of another platform has the same result.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
demonfox
demonfox
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1515 Visits: 1192
Hugo Kornelis (3/21/2013)
Toreador (3/21/2013)
Using cascade delete. If the business rule says that is the appropriate behaviour, then why avoid using inbuilt functionality that achieves exactly what you need?

One possible reason is that you can't control the order of locks being taken with cascading deletes and cascading updates. That makes it harder to avoid deadlock scenarios if you are in a high concurrency scenario.

Other than that, I see no reason not to use this feature *IF* that is what the business wants. However, in my experience the number of scenarios where the business actually wants to delete dependent rows automatically is very small.


+1 .. I have been, in the past, specifically asked to avoid cascade delete (after I suggested them, that's a different case :-D ).

if the business say so .. I am not sure ..

I really can't think of a scenario where business would allow that .. although, I would definitely code it for Police database , if they got a record of mine :-D

~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one Ermm
demonfox
demonfox
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1515 Visits: 1192
One possible reason is that you can't control the order of locks being taken with cascading deletes and cascading updates. That makes it harder to avoid deadlock scenarios if you are in a high concurrency scenario.



I didn't know that , I need to research more ..

thanks for the information Hugo !!!!!

~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one Ermm
db4breakfast
db4breakfast
SSC-Addicted
SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)

Group: General Forum Members
Points: 467 Visits: 389
demonfox (3/21/2013)
Dineshbabu (3/20/2013)
Lokesh Vij (3/20/2013)
db4breakfast (3/20/2013)
I never use cascade delete on a production db. It seem to be safer by allowing users mark order as "error" instead.


+1
same here. i feel it is dangerous to use on delete cascade in production...as a mistake in deleting a parent records will trigger deletes from all the child records w00t


Why are you allowing a person to delete who is prone to do mistake in production environment?????

we should always try to avoid manual DML operations in production environment. Everything has to be done through application. If really required can do it with in BEGIN TRAN...


I am not sure what he meant by that , but sometime because of some junk records , a delete may be carried out as a quick solution ..

and why there is junk records ? It can happen .. but may be it's an update cascade scenario fix.. may be a delete and reload ...

Edit :


To be clear, we don't allow users to delete any record in our application. However there's a function for users to flag a record as error. Instead of cascade delete, the application just flag the record as "error" in the parent table. All data are still there.

--------------------------------------------------------------------------------------
Hai Ton
My Db4Breakfast blog.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search