CASCADE - 1

Question

Post reply

CASCADE - 1

Ron McCullough

SSC Guru

Points: 63877
More actions
March 19, 2013 at 10:31 pm

#291008

Comments posted to this topic are about the item CASCADE - 1
If everything seems to be going well, you have obviously overlooked something.
Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Viewing 15 posts - 1 through 15 (of 53 total)

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

demonfox SSCertifiable Points: 6289 More actions · Answer 1

All that sql statemens were just for the final show 🙂

Nice and easy !!

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

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

I expected there will be some trickery work will be there.. But it's easy and staright forward question..

--
Dineshbabu
Desire to learn new things..

Lokesh Vij SSChampion Points: 10836 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..

+1

Nice and easy. Thanks Ron!

~ 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

Vinay Kumar SSCertifiable Points: 6099 More actions · Answer 4

Good question.

In real life scenario, we need to take care of cascade option.

Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!

demonfox SSCertifiable Points: 6289 More actions · Answer 5

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

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

Vinay Kumar SSCertifiable Points: 6099 More actions · Answer 6

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

:-):-):-)

Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 7

Nice basic question Ron, thanks!

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

peter.row SSCarpal Tunnel Points: 4319 More actions · Answer 8

Basic question designed to seem like a trick?

This applies equally to SQL Server 2005 and 2008 not just 2008R2 and 2012.

Stuart Davies SSCoach Points: 19505 More actions · Answer 9

Nice and clear question - thanks Ron

-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx

Carlo Romagnano SSC-Insane Points: 22668 More actions · Answer 10

I do not like "[OrderID] [int] NULL," in the [OrderDetail],

so you can insert orphan records despite the constraint.

CREATE TABLE [dbo].[OrderDetail]

(

[OrderDetailID] [int] NOT NULL,

[OrderID] [int] NULL,

CONSTRAINT [PK_OrderDetail] PRIMARY KEY CLUSTERED ([OrderDetailID] ASC)

)

The rest is basic!

😀

Raghavendra Mudugal SSChampion Points: 10658 More actions · Answer 11

Simply Nice, thank you for posting.

(i knew this before, and just fews days ago i kind of helped a person online to fix this, so it was easy recall for me)

ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

Raghavendra Mudugal SSChampion Points: 10658 More actions · Answer 12

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.

also... including our old friend SQL SERVER 2000

ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

TomThomson SSC Guru Points: 104773 More actions · Answer 13

It's a nice clear question with a nice unambiguous answer.

I'm a little bothered by the statement that on delete cascade means that if rows in several tables reference a key they will all be deleted; on delete cascade applies only to a single foreign key relationship, so can't influence the action on multiple referring tables; the foreign key relationships on other referencing tables might have SET NULL or SET DEFAULT instead of CASCADE and then the rows in those tables wouln't be deleted, or might even have NO ACTION in which case the delete wouldn't happen for any rows at all. ON CASCADE only means that referring rows in this table will be deleted if deletion of the target is successful.

Carlo Romagnano (3/20/2013)
I do not like "[OrderID] [int] NULL," in the [OrderDetail],
so you can insert orphan records despite the constraint.

It's a good idea to avoid NULL if possible, of course, but sometimes in the real world all the data isn't available when something is inserted and in that case a NULL may be needed - of course it does seem unlikely that the order of which the detail is part will be unknown, but I can easily dream up a zany ordering process in which it would almost always be unknown (and on reflection I can envisage such an ordering process as srather useful in some contexts) so I can't object to a nullable column here. Anyway, you can't insert orphan rows; you can insert rows which look like orphan rows, but they aren't orphan rows because an orphan row is one that has been orphaned - it had a parent once, but the parent has disappeared; so a reference constraint with on update cascade does prevent real orphan rows, it just doesn't prevent insertion of rows that look as if they are orphan rows.

The thing I might complain about if someone suggested actually doing it is the structured orderdetailid field; I can't tell from the table definition that the orderdetail table isn't in 1NF, but I can tell from the insertion code that it definitely is not in 1NF (and just to placate the anti-null fundamentalists, I'll point out that this remains true if NULL isn't permitted in any of the columns). And of course a consequence of that structured column being the primary key is that it can never be null, which makes it an absolute nonsense that one of the components that go to make up the structured column is allowed to be null. But none of that is what the question is about.

Tom

TomThomson SSC Guru Points: 104773 More actions · Answer 14

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?

Tom