January 23, 2010 at 1:59 pm
Comments posted to this topic are about the item Declarative Data Integrity
January 24, 2010 at 7:20 pm
The explanation, while by itself is accurate, in the context of this question does not really provide an adequate explanation.
A better explanation might be explaining how, with cascading deletes, how deleting countryID 1 from the countries tables deletes the cities with that countryid, and then the buyers with the affected city codes are deleted.
Edit: I also curious... how much are cascading updates/deletes used in a production environment? Personally, I've never seen it actually used, so I'm curious as to how much it is used.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
January 24, 2010 at 8:30 pm
From Steve Jones blog post on the Question of the Day
A few notes on submitting a question:
Don’t get too cute, keep your code and the question simple.
Specify the version if you use something that might be version specific
Get a reference, preferably from Books Online, that explains and documents what happened. If you don’t know, post a note in a forum and see if someone can help you understand why things happen.
Use BOL online, not the ms-help:// local reference.
If you want people to choose more than one answer, say that.
Use a spell/grammar checker.
Emphasis added by BitBucket-25253
I therefor presented as the supporting document the online page from BOL which contains
ON DELETE CASCADE
Specifies that if an attempt is made to delete a row with a key referenced by foreign keys in existing rows in other tables, all rows that contain those foreign keys are also deleted.
Which seems, in my humble opinion,(and at this time 54 percent of those answering the question) an adequate explanation, unless of course one's education stopped before high school graduation.
January 25, 2010 at 12:49 am
Good question.
Not a good explanation, though.
"The constriaints prevent some inserts and deletes from occurring." - I did not run the code, but I am pretty sure that all inserts succeed, as does the delete.
The explanation should have stated that the cascading foreign key constraints cause the deletion of a single country to delete two cities and three customers as well.
January 25, 2010 at 12:52 am
WayneS (1/24/2010)
Edit: I also curious... how much are cascading updates/deletes used in a production environment? Personally, I've never seen it actually used, so I'm curious as to how much it is used.
I think it's used very little. And there are two good reasons.
1) Most businesses do not want information to be deleted automatically. In fact, many organisations prefer not to delete data at all, moving it to an archive table instead. Just in case. Somewhat akin to the concept of not removing code that is no longer needed, but commenting it out instead.
2) In heavily used OLTP systems, one of the best practises for minimising deadlocks is to ensure that table locks are always taken in the same order. With cascading constraints, multiple tables are locked in an order beyond your control, so you increase the deadlock chance. You can avoid that by coding the cascading delete yourself instead of using DRI.
January 25, 2010 at 1:32 am
bitbucket-25253 (1/24/2010)
<snip>
I therefor presented as the supporting document the online page from BOL which contains
ON DELETE CASCADE
Specifies that if an attempt is made to delete a row with a key referenced by foreign keys in existing rows in other tables, all rows that contain those foreign keys are also deleted.
Which seems, in my humble opinion,(and at this time 54 percent of those answering the question) an adequate explanation.
True. Therefore, the bit from BOL you quote here would be a much more apposite "explanation" of the answer than the one-liner you used, which though absolutely true, is utterly irrelevant to the question being posed.
I do agree that this was a nice question - made me think and double-check my thinking before I answered it.
Kelsey Thornton
MBCS CITP
January 25, 2010 at 2:41 am
Hugo Kornelis (1/25/2010)
Good question.Not a good explanation, though.
"The constriaints prevent some inserts and deletes from occurring." - I did not run the code, but I am pretty sure that all inserts succeed, as does the delete.
The explanation should have stated that the cascading foreign key constraints cause the deletion of a single country to delete two cities and three customers as well.
You are absolutely correct! The inserts and deletes are successful, and the cascading delete is the cause of the result. The answer is correct, but the explanation is wrong.
/Håkan Winther
MCITP:Database Developer 2008
MCTS: SQL Server 2008, Implementation and Maintenance
MCSE: Data Platform
January 25, 2010 at 7:33 am
WayneS (1/24/2010)
.................Edit: I also curious... how much are cascading updates/deletes used in a production environment? Personally, I've never seen it actually used, so I'm curious as to how much it is used.
The only use of it I have seen was one that I created for a set of 4 tables used as an intermediate stage for transitioning data from 1 application on a UNIX based system to another on a Windows based SQL Server. The data had no user interface to access it directly, but was updated in both directions synchronously. The data persisted until all references to it were gone from both systems, and at that point there was no reason not to clean it up with cascading deletes.
I think there is a good argument on NOT using cascading updates/deletes on any set that has procedures for updating or deleting the child rows. It just would not be logical to use both.
January 25, 2010 at 8:19 am
I responded with the correct answer (2, 1, 0) but was marked Wrong!
BTW, the code will only run under SQL Server 2008, which wasn't stated. And it will successfully execute.
January 25, 2010 at 8:30 am
WayneS (1/24/2010)
Edit: I also curious... how much are cascading updates/deletes used in a production environment? Personally, I've never seen it actually used, so I'm curious as to how much it is used.
I've used cascading deletes (ON DELETE CASCADE) extensively in the numerous database systems that I've designed and built over the decades. However, it is not enabled by default. I.e., all constraints are initially "blocking" constraints. Then during the [business logic] design of the application, where it is proper for the cascading delete to occur, the constraint is changed.
On the other hand, I've rarely used a cascade update.
January 25, 2010 at 8:30 am
Mauve (1/25/2010)
I responded with the correct answer (2, 1, 0) but was marked Wrong!BTW, the code will only run under SQL Server 2008, which wasn't stated. And it will successfully execute.
Are you sure you didn't accidentally click another option? I answered (2, 1, 0) and got my point.
January 25, 2010 at 8:40 am
Mauve
BTW, the code will only run under SQL Server 2008, which wasn't stated. And it will successfully execute.
The question is not wether the CREATE TABLE and INSERT INTO statements would execute on SQL 2005.
The question was designed to test the users knowledge of the effect of ON DELETE CASCADE without running the code.
If the user did want to run the code on 2005, and did not know the older syntax, and since the object of the QOD is to teach, those users would have learned two (2) things.
January 25, 2010 at 8:43 am
Hugo Kornelis
Not a good explanation, though
Ditto for Kelsey Thornton
Did you click on the supporting documentation link to read the full explanation?
January 25, 2010 at 8:49 am
bitbucket-25253 (1/25/2010)
Hugo Kornelis
Not a good explanation, though
Ditto for Kelsey Thornton
Did you click on the supporting documentation link to read the full explanation?
Did you read what I actually wrote?
The explanation says that the constraints prevent some inserts and deletes from occuring. Pray tell me, bitbucket - exactly which inserts and deletes are prevented by these constraints?
PS: Yes, I did click the link. It said exactly what I already knew about cascading constraints.
January 25, 2010 at 8:51 am
bitbucket-25253 (1/25/2010)
Hugo Kornelis
Not a good explanation, though
Ditto for Kelsey Thornton
Did you click on the supporting documentation link to read the full explanation?
If you read my post it is pretty evident that I did.
What I am saying is that the fact that "constraints prevent some inserts and deletes from occuring" is utterly irrelevant, as these constraints are not acting.
I must reiterate that I thought this was a GOOD question.
I am NOT trying to "shoot you down", or anything like that - it takes a lot of guts to submit a QOTD - especially at that moment!
Kelsey Thornton
MBCS CITP
Viewing 15 posts - 1 through 15 (of 42 total)
You must be logged in to reply to this topic. Login to reply