Delete parent record

Question

Delete parent record

ShekharNaidu

SSCrazy

Points: 2092
More actions
May 27, 2010 at 8:28 am

#156987

Hi all
In a recent interview I had in some organization, i got this question.
How delete a parent table record without deleting child table record.
can you please any one tell me how to do it.
Thanx in advance.
Thanks ,
Shekhar

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

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

Lowell SSC Guru Points: 323504 More actions · Answer 1

you can't without breaking referential integrity.

Otherwise you'd be dropping the foreign key constraint, or disabling the foreign key constraint so it would be invalid after you deleted the parent record.

maybe they were trying to get you to say you'd have to set the child FK to NULL before you delete the parent?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ShekharNaidu SSCrazy Points: 2092 More actions · Answer 2

they said we have way of doing it..without disabling/deleting the FK..

I asked for the answer...but he said..."it is good if you find out urself"..:crying:

Thanks ,

Shekhar

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 3

You'd have to move the child, disable the FK, or drop the FK. My guess is they are moving the child to another record, or a holder record.

Or they don't have FKs defined and are enforcing them in the app only.

Paul White SSC Guru Points: 150467 More actions · Answer 4

Cascading DELETE with an action of SET NULL or SET DEFAULT

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

ZenDada SSCarpal Tunnel Points: 4637 More actions · Answer 5

Paul White NZ (5/27/2010)
Cascading DELETE with an action of SET NULL or SET DEFAULT

Right answer. 10 points.

[font="Courier New"]ZenDada[/font]

Paul White SSC Guru Points: 150467 More actions · Answer 6

ZenDada (5/27/2010)
Right answer. 10 points.

Steve!!! Ten points please!

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

ShekharNaidu SSCrazy Points: 2092 More actions · Answer 7

hey paul..

if you dont mind...can you please elobarate...

Thanks ,

Shekhar

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 8

Did not know that, though I've avoided cascading deletes and updates for years.

Does anyone use this?

Paul White SSC Guru Points: 150467 More actions · Answer 9

ShekharNaidu (5/27/2010)
hey paul..
if you dont mind...can you please elobarate...

Sure, there's a whole section of Books Online about it:

http://technet.microsoft.com/en-us/library/ms186973.aspx

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Jack Corbett SSC Guru Points: 184393 More actions · Answer 10

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
Consultant - Straight Path Solutions
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

Paul White SSC Guru Points: 150467 More actions · Answer 11

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
SQLkiwi blog
@SQL_Kiwi

Jack Corbett SSC Guru Points: 184393 More actions · Answer 12

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
Consultant - Straight Path Solutions
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

Paul White SSC Guru Points: 150467 More actions · Answer 13

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
SQLkiwi blog
@SQL_Kiwi

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 14

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.