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 12»»

Delete parent record Expand / Collapse
Author
Message
Posted Thursday, May 27, 2010 8:28 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:16 PM
Points: 12,755, Visits: 31,121
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

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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"..



Thanks ,

Shekhar

Post #929032
Posted Thursday, May 27, 2010 8:54 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 2:02 PM
Points: 32,834, Visits: 14,974
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

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 5:04 PM
Points: 11,168, Visits: 10,939
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

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, August 21, 2013 9:19 AM
Points: 434, Visits: 271
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

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 5:04 PM
Points: 11,168, Visits: 10,939
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

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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
Posted Thursday, May 27, 2010 9:09 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 2:02 PM
Points: 32,834, Visits: 14,974
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

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 5:04 PM
Points: 11,168, Visits: 10,939
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 12»»

Permissions Expand / Collapse