SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Delete parent record


Delete parent record

Author
Message
Jack Corbett
  Jack Corbett
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19056 Visits: 14900
I've never used Cascading updates or deletes. I think that there is a reason for the FK and that I shouldn't just blindly update or delete child information. If there is a business reason for deleting the parent then there should be business rules enforced in the application for it. I don't think I should be able to do the delete outside the application in that case.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Paul White
Paul White
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16166 Visits: 11355
Jack Corbett (5/27/2010)
I've never used Cascading updates or deletes. I think that there is a reason for the FK and that I shouldn't just blindly update or delete child information. If there is a business reason for deleting the parent then there should be business rules enforced in the application for it. I don't think I should be able to do the delete outside the application in that case.

I tend to agree, but this was an interview question to check technical knowledge and/or lateral thinking.
I've only ever used SET NULL, and both times were a work around in a 3rd party system.



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Jack Corbett
  Jack Corbett
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19056 Visits: 14900
Paul White NZ (5/27/2010)
Jack Corbett (5/27/2010)
I've never used Cascading updates or deletes. I think that there is a reason for the FK and that I shouldn't just blindly update or delete child information. If there is a business reason for deleting the parent then there should be business rules enforced in the application for it. I don't think I should be able to do the delete outside the application in that case.

I tend to agree, but this was an interview question to check technical knowledge and/or lateral thinking.
I've only ever used SET NULL, and both times were a work around in a 3rd party system.


I should have quoted Steve's post asking if anyone's used it.

Yes, for an interview answer cascading deletes with SET NULL would be the best answer. Even with this I'd rather enforce it in a stored procedure or business layer, if not using stored procedures. Of course both assume that the column in the child is nullable.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Paul White
Paul White
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16166 Visits: 11355
Jack Corbett (5/27/2010)
I should have quoted Steve's post asking if anyone's used it.

Oh, right, gotcha :-)

Yes, for an interview answer cascading deletes with SET NULL would be the best answer. Even with this I'd rather enforce it in a stored procedure or business layer, if not using stored procedures. Of course both assume that the column in the child is nullable.

It also assumes that NULL and the DEFAULT value are valid parent keys...this usually prompts a long discussion about whether RI is violated by allowing this behaviour. My view is that if the FK relationship stays valid, there's RI. Not everyone agrees with me.



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Steve Jones
Steve Jones
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: Administrators
Points: 64417 Visits: 19117
My view is the PKs, FKs, are based on business rules. You can't define an OrderItem to be definitely linked to an Order without a business rule. So if the business rule allows defaults or NULLs for the FK, then it doesn't violate RI.

However, that somehow assumes that the person performing the update really understands the business rules, and has correctly explained this to the business and gotten agreement. That's where I'd be concerned.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
ZenDada
ZenDada
Mr or Mrs. 500
Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)

Group: General Forum Members
Points: 576 Visits: 320
Here's what we do. My tables have an is_delete bit on them. When a record's is_delete bit is set on or off, a trigger cascades the is_delete bit to any dependent records in other tables. Sadly, the cascading feature that is available does not support this, so a trigger is needed. Sad But that is our solution. I record these cascades in an audit trail table so I can follow the chain and report on who did it and which dependent tables were affected.

ZenDada
ShekharNaidu
ShekharNaidu
SSC-Enthusiastic
SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)

Group: General Forum Members
Points: 194 Visits: 70
thanx to all of you... for spending ur time... helping me out...


Thanks ,

Shekhar

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