Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««23456»»»

CASCADE - 1 Expand / Collapse
Author
Message
Posted Thursday, March 21, 2013 3:09 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 8:03 AM
Points: 825, 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


We use triggers to delete the child records when a parent record is deleted. Is that generally considered a bad thing to do?
Post #1433646
Posted Thursday, March 21, 2013 3:14 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 8:03 AM
Points: 825, 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?
Post #1433647
Posted Thursday, March 21, 2013 3:34 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:46 PM
Points: 5,998, Visits: 8,261
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
Post #1433659
Posted Thursday, March 21, 2013 3:38 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:46 PM
Points: 5,998, Visits: 8,261
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
Post #1433662
Posted Thursday, March 21, 2013 3:41 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 8:03 AM
Points: 825, 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).
Post #1433666
Posted Thursday, March 21, 2013 3:48 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:46 PM
Points: 5,998, Visits: 8,261
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
Post #1433676
Posted Thursday, March 21, 2013 3:51 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:46 PM
Points: 5,998, Visits: 8,261
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. ;)
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
Post #1433680
Posted Thursday, March 21, 2013 5:02 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, July 31, 2014 7:15 PM
Points: 1,129, Visits: 1,163
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 ).

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



~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one
Post #1433714
Posted Thursday, March 21, 2013 5:05 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, July 31, 2014 7:15 PM
Points: 1,129, Visits: 1,163
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
Post #1433715
Posted Thursday, March 21, 2013 6:45 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, September 5, 2014 3:53 PM
Points: 386, Visits: 364
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


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.
Post #1433755
« Prev Topic | Next Topic »

Add to briefcase «««23456»»»

Permissions Expand / Collapse