January 25, 2010 at 9:01 am
Thanks, Kelsey. You write exactly what I intended to write, but in a much nicer tone.
Bitbucket, if my post came across too harsh, I apologise for the tone.
January 25, 2010 at 9:16 am
Hugo Kornelis and Kelsey Thornton
It is my turn to apologize .... I thank both of you for your constructive comments .... (none were harsh)
I too learned something from the QOD (now how to get Steve to award a point or two - said in jest of course)
In the future I will when appropriate not copy a snippet of the explanation, but rather just post a link to the supporting documentation.
Again THANKS
January 25, 2010 at 9:20 am
Thanks, Bitbucket!
And thanks for submitting the QotD as well. I have submitted a few of my own, so I know how much energy goes into it - and how, despite all your efforts, you are always bound to displease at least SOME people... :crying:
January 25, 2010 at 9:29 am
bitbucket-25253 (1/25/2010)
<snip>In the future I will when appropriate not copy a snippet of the explanation, but rather just post a link to the supporting documentation.
I think an appropriate snippet would be better than simply a link.
e.g. the snippet you posted in your first reply would have been perfect!
Again - thanks for exercising the little grey cells.
Kelsey Thornton
MBCS CITP
January 25, 2010 at 2:58 pm
Kelsey Thornton (1/25/2010)
bitbucket-25253 (1/25/2010)
<snip>In the future I will when appropriate not copy a snippet of the explanation, but rather just post a link to the supporting documentation.
I think an appropriate snippet would be better than simply a link.
e.g. the snippet you posted in your first reply would have been perfect!
Again - thanks for exercising the little grey cells.
I have to agree with Kelsey...
I think that
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.
would have been much better than
constraints prevent some inserts and deletes from occuring
Which is what I was trying to say in my first post.
What I didn't say was that this was a GREAT QOD. It made me think it through.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
January 25, 2010 at 3:40 pm
Thanks bitbucket for providing yet another QoTD.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 26, 2010 at 1:59 pm
Oops, I misread the last statement as a join on the first table, not the second one. It makes sense now. Staring at SQL all day messes with your head, I've decided. :hehe:
January 27, 2010 at 2:22 am
CoUNTRY1 COUNTRY2 COUNTRY3 TBL1
/ \ /
City1 City2 City3 TBL2
/ \ Buyer1 Buyer2 Buyer3 TBL3
Above is my structure. This is more lyk a diagram and by looking at this:
If you remove all that is from COUNTRY1 and its descendants then in TBL1 you'll have 2 records and in TBL2 you will have 1 record and none in TBL3. See below, everything from far left as per above...:);-)
COUNTRY2COUNTRY3TBL1
/
CITY3TBL2
TBL3
January 28, 2010 at 7:00 am
Well that just seems like a waste...having to write cascading deletes/updates yourself, considering that it is already built in.
February 14, 2010 at 9:05 am
WayneS (1/24/2010)
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.
Yes, it's a good clear question, a correct answer, and an "explanation" which doesn't explain anything at all about why that is the correct answer; and the explanation isn't even factually correct - the constraints used in the question can't prevent any deletions.
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.
Hard to say: I've used them (edit: cascading deletes, not cascading updates)in a production environment, and have seem them used in production by others. I suspect it's used quite a lot, to simplify deletion, because without it good referential integrity checking can be a real pain to provide. But I've known people who swore it was an extremely dangerous feature and would not permit it to be used. I suspect that most people will recognise its usefulness and use it where approptiate.
Tom
February 14, 2010 at 9:54 am
Tom.Thomson
Yes, it's a good clear question, a correct answer, and an "explanation" which doesn't explain anything at all about why that is the correct answer
From the reference cited in the
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.
A definition of CASCADE - to move on to others in succession
I think the explanation was valid and quite concise - if not, argue with the Microsoft individual(s) who composed the SQL Server 2008 Books Online topic
February 14, 2010 at 2:08 pm
bitbucket-25253 (2/14/2010)
Tom.Thomson
Yes, it's a good clear question, a correct answer, and an "explanation" which doesn't explain anything at all about why that is the correct answer
From the reference cited in the
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.
Yes, that's what it says.
A definition of CASCADE - to move on to others in succession
I think the explanation was valid and quite concise
Then I guess you can explain how a reference constraint with on delete cascade can prevent deletions if, as in this case, there isn't a constraint without on delete cascade at the end of the chain?
- if not, argue with the Microsoft individual(s) who composed the SQL Server 2008 Books Online topic
why should I argue with them when I completely agree with their very clear and plain description?
Tom
February 14, 2010 at 2:53 pm
bitbucket-25253 (2/14/2010)
--------------------------------------------------------------------------------
Tom.Thomson
Yes, it's a good clear question, a correct answer, and an "explanation" which doesn't explain anything at all about why that is the correct answer
emphasis above added by this poster.
why should I argue with them when I completely agree with their very clear and plain description?
Isn't that just what I gave in the Explanation? At one time you say the explanation not saying enough and yet you state that MS description you agree with? Got me confused you do
February 15, 2010 at 6:14 am
bitbucket-25253 (2/14/2010)
Isn't that just what I gave in the Explanation? At one time you say the explanation not saying enough and yet you state that MS description you agree with? Got me confused you do
Well, actually, no. The explanation said
The constriaints prevent some inserts and deletes from occurring.
That's very different from saying that the constraints prevent some inserts from occurring and cause some extra deletes to occur (and, as the all the constraints have "on delete cascade" they can't ever prevent a delete from occurring, because the cascades ensure that the deletions can occur without violating the constraints). I'm sorry my previous explanation wasn't clear enough - at lthe east I should have quoted the explanation that I was commenting on to avoid anyone thinking my comment was about the URL referenced.
Tom
February 15, 2010 at 6:24 am
Tom.Thomson
Thank you for the clarification -- now I understand your point.
As one objective of the QOD is to teach - I have learned a bit more about wording the explanation.
For that I say Thank you.
Hopefully those that answer the question from this point forward and read this forum will also learn a bit more.
Viewing 15 posts - 16 through 30 (of 42 total)
You must be logged in to reply to this topic. Login to reply