Delete parent record

Click here to monitor SSC

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>

SQLServerCentral is supported by Red Gate Software Ltd.

Log in :: Register :: Not logged in

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>

Recent Posts

Popular Topics

Home

Home » SQL Server 2005 » T-SQL (SS2K5) » Delete parent record

Add to briefcase

17 posts, Page 1 of 2

1 2 »»

Delete parent record

Expand / Collapse

Author

Message

Posted Thursday, May 27, 2010 8:28 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, November 08, 2010 10:45 PM
Points: 122, Visits: 70

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

Post #929023

Posted Thursday, May 27, 2010 8:32 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 9:24 AM
Points: 11,791, Visits: 28,073

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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #929027

Posted Thursday, May 27, 2010 8:44 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, November 08, 2010 10:45 PM
Points: 122, Visits: 70

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

Crying

Thanks ,

Shekhar

Post #929032

Steve Jones - SSC Editor

Posted Thursday, May 27, 2010 8:54 AM

SSC-Dedicated

Group: Administrators
Last Login: Today @ 5:09 AM
Points: 31,526, Visits: 13,864

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.

Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help

Post #929046

Posted Thursday, May 27, 2010 9:01 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 10:48 AM
Points: 10,990, Visits: 10,578

Cascading DELETE with an action of SET NULL or SET DEFAULT

Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi

Post #929052

Posted Thursday, May 27, 2010 9:03 AM

SSC-Addicted

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 1:50 PM
Points: 434, Visits: 270

Paul White NZ (5/27/2010)

Cascading DELETE with an action of SET NULL or SET DEFAULT

Right answer. 10 points.

ZenDada

Post #929057

Posted Thursday, May 27, 2010 9:08 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 10:48 AM
Points: 10,990, Visits: 10,578

ZenDada (5/27/2010)

Right answer. 10 points.

Steve!!! Ten points please!

Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi

Post #929065

Posted Thursday, May 27, 2010 9:09 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, November 08, 2010 10:45 PM
Points: 122, Visits: 70

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

Thanks ,

Shekhar

Post #929068

Steve Jones - SSC Editor

Posted Thursday, May 27, 2010 9:09 AM

SSC-Dedicated

Group: Administrators
Last Login: Today @ 5:09 AM
Points: 31,526, Visits: 13,864

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

Does anyone use this?

Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help

Post #929069

Posted Thursday, May 27, 2010 9:17 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 10:48 AM
Points: 10,990, Visits: 10,578

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
SQL Server MVP
SQLblog.com
@SQL_Kiwi

Post #929076

« Prev Topic | Next Topic »

Add to briefcase

17 posts, Page 1 of 2

1 2 »»

Permissions

Expand / Collapse