Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


CASCADE - 1


CASCADE - 1

Author
Message
Carlo Romagnano
Carlo Romagnano
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3627 Visits: 3236
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!
:-D
Raghavendra Mudugal
Raghavendra Mudugal
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1690 Visits: 2958
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
Raghavendra Mudugal
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1690 Visits: 2958
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
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10765 Visits: 12019
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
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10765 Visits: 12019
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

(Bob Brown)
(Bob Brown)
SSC Eights!
SSC Eights! (819 reputation)SSC Eights! (819 reputation)SSC Eights! (819 reputation)SSC Eights! (819 reputation)SSC Eights! (819 reputation)SSC Eights! (819 reputation)SSC Eights! (819 reputation)SSC Eights! (819 reputation)

Group: General Forum Members
Points: 819 Visits: 1145
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
Carlo Romagnano
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3627 Visits: 3236
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
Thomas Abraham
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2347 Visits: 2254
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.

Please don't go. The drones need you. They look up to you.
Connect to me on LinkedIn
Victor Kirkpatrick
Victor Kirkpatrick
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2128 Visits: 446
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
mtassin
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4109 Visits: 72512
I kept looking for the gotcha.... couldn't find it.... because it wasn't there. Good question. Smile



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


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search