CASCADE - 1

Question

CASCADE - 1

Viewing 15 posts - 16 through 30 (of 53 total)

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

(Bob Brown) SSCrazy Points: 2705 More actions · Answer 1

Nice easy question. I was looking for the trick and was very careful to study the data in the two tables but it turned out to be straight forward. Thanks, good one!

Carlo Romagnano SSC-Insane Points: 22680 More actions · Answer 2

L' Eomot Inversé (3/20/2013)
demonfox (3/20/2013)
Danny Ocean (3/20/2013)
Good question.
In real life scenario, we need to take care of cascade option.
I prefer not to have delete cascade on design .. to let the error display when the constraint doesn't allow it..
well, delete cascase could be a better option for delete based on performance , but with proper indexing , I guess, basic deletes shouldn't be too intensive process ..
and , it keeps of mistakes off the track 🙂 that's what constraints are for ...
No, constraints are to ensure that the schema enforces its own integrity and that you don't have to write code to enforce it. It means that you can't make mistakes that would lead to an invalid state of the schema, not that you can't make mistakes at all.
Avoiding ON DELETE CASCADE is usually a mistake; it requires code to be written to do something the system could do for you - and that code costs development time and testing time and integration time and may contain bugs (for example sometimes deleting some rows in the referring table that it shouldn't delete, or sometimes not deleting all the referring rows that it should delete, or deleting all the referring rows but omitting deletion of the referred row) which can have highly undesirable results. If you are worried about bugs that delete the wrong order, will increasing the code size and complexity prevent those bugs or do nothing to stop them while introducing potentilly more bugs?
If on the other hand you are worried about someone carelessly doing a manual delete on an order row that shouldn't be deleted, why do your procedures allow manual deletes? If you need manual deletes, is forcing people to use a much more error prone manual process (find the detail rows and delete them first, then delete the order row) instead of a simple manual process (just delete the order row and let the system take care of the rest) going to make them more likely or less likely to make mistakes? If you don't want certain things to be deleted all, why does anyone have delete permissions on them in the first place? Is it possible that in eschewing use of ON UPDATE CASCADE you are trying to fix operational issues involving people, which is usually (always, as far as Iknow) not something you can do in a computer program?

++++1

Thomas Abraham SSChampion Points: 10761 More actions · Answer 3

Dineshbabu (3/19/2013)
I expected there will be some trickery work will be there.. But it's easy and staright forward question..

I too looked for the trick. But, it's only easy if you are familiar with CASCADE. Fortunately, I was. Thanks Ron.

[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn

Victor Kirkpatrick Hall of Fame Points: 3672 More actions · Answer 4

peter.row (3/20/2013)
Basic question designed to seem like a trick?
This applies equally to SQL Server 2005 and 2008 not just 2008R2 and 2012.

Exactly... delete cascade is available in 2005 & regular 2008, so I was looking for some kind of trick. Glad to see there wasn't one.

mtassin SSC-Insane Points: 23099 More actions · Answer 5

I kept looking for the gotcha.... couldn't find it.... because it wasn't there. Good question. 🙂

--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url]

Dana Medley SSCertifiable Points: 6764 More actions · Answer 6

mtassin (3/20/2013)
I kept looking for the gotcha.... couldn't find it.... because it wasn't there. Good question. 🙂

+ 1 Thanks for the great question.

Everything is awesome!

demonfox SSCertifiable Points: 6289 More actions · Answer 7

L' Eomot Inversé (3/20/2013)
demonfox (3/20/2013)
Danny Ocean (3/20/2013)
Good question.
In real life scenario, we need to take care of cascade option.
I prefer not to have delete cascade on design .. to let the error display when the constraint doesn't allow it..
well, delete cascase could be a better option for delete based on performance , but with proper indexing , I guess, basic deletes shouldn't be too intensive process ..
and , it keeps of mistakes off the track 🙂 that's what constraints are for ...
No, constraints are to ensure that the schema enforces its own integrity and that you don't have to write code to enforce it. It means that you can't make mistakes that would lead to an invalid state of the schema, not that you can't make mistakes at all.
Avoiding ON DELETE CASCADE is usually a mistake; it requires code to be written to do something the system could do for you - and that code costs development time and testing time and integration time and may contain bugs (for example sometimes deleting some rows in the referring table that it shouldn't delete, or sometimes not deleting all the referring rows that it should delete, or deleting all the referring rows but omitting deletion of the referred row) which can have highly undesirable results. If you are worried about bugs that delete the wrong order, will increasing the code size and complexity prevent those bugs or do nothing to stop them while introducing potentilly more bugs?

Yes, these are all bugs .. it could happen either way ; which way do you think is more safe ??

Besides for a more I-U process and less of a D process , these cascade otpions are not that much required ..

I always thought of delete cascade as a cool funcionality to be used for clean up processes and only when I am pretty sure what I am doing ...

~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:

db4breakfast Ten Centuries Points: 1097 More actions · Answer 8

I spent more time to figure out the values of orderdetailID than looking at the effect of the cascade delete. The question title can be misleading sometime. 😀

I never use cascade delete on a production db. It seem to be safer by allowing users mark order as "error" instead.

Thanks for the question, Ron.

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

sknox SSChampion Points: 12388 More actions · Answer 9

Stuart Davies (3/20/2013)
Nice and clear question - thanks Ron

Yes, but he did nothing to account for those of us who read through the question, came to the correct conclusion, and then misread the answer choices! Can't this technology stuff tell what we mean rather than what we say by now!?

😛

Lokesh Vij SSChampion Points: 10836 More actions · Answer 10

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:

~ Lokesh Vij

Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com[/url]

Follow me @Twitter

jlennartz SSCommitted Points: 1574 More actions · Answer 11

Thanks You for the nice clear question and answer.

Dineshbabu Hall of Fame Points: 3220 More actions · Answer 12

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...

--
Dineshbabu
Desire to learn new things..

demonfox SSCertifiable Points: 6289 More actions · Answer 13

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 :

~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:

Toreador SSChampion Points: 11368 More actions · Answer 14

demonfox (3/20/2013)
which way do you think is more safe ??

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?

marlon.seton SSCrazy Points: 2623 More actions · Answer 15

peter.row (3/20/2013)
Basic question designed to seem like a trick?
This applies equally to SQL Server 2005 and 2008 not just 2008R2 and 2012.

Might 2008R2 and 2012 have been specified because those were the only systems against which a test was made before posting the QoD?