Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

CASCADE - 1 Expand / Collapse
Author
Message
Posted Wednesday, March 20, 2013 2:34 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:53 AM
Points: 2,618, Visits: 2,466
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!
Post #1433061
Posted Wednesday, March 20, 2013 2:59 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 9:43 PM
Points: 1,053, Visits: 2,354
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.
Post #1433068
Posted Wednesday, March 20, 2013 3:04 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 9:43 PM
Points: 1,053, Visits: 2,354
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.
Post #1433070
Posted Wednesday, March 20, 2013 3:26 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:11 PM
Points: 7,920, Visits: 9,646
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
Post #1433079
Posted Wednesday, March 20, 2013 3:48 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:11 PM
Points: 7,920, Visits: 9,646
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
Post #1433096
Posted Wednesday, March 20, 2013 4:28 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, June 12, 2014 4:19 AM
Points: 701, Visits: 1,145
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!
Post #1433118
Posted Wednesday, March 20, 2013 4:53 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:53 AM
Points: 2,618, Visits: 2,466
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
Post #1433130
Posted Wednesday, March 20, 2013 5:06 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 9:45 AM
Points: 1,921, Visits: 2,203
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
Post #1433133
Posted Wednesday, March 20, 2013 5:21 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, December 11, 2014 5:07 AM
Points: 1,882, Visits: 375
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.
Post #1433138
Posted Wednesday, March 20, 2013 7:01 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 7:42 AM
Points: 3,688, Visits: 72,435
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
For tips on how to post your problems
Post #1433185
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse