July 1, 2009 at 3:26 pm
Can someone send the script for : SQL automatically cascade deletes to child data
Is there any way to delete records without dropping FKs
July 1, 2009 at 3:36 pm
You could disable the foreign keys using something like:
ALTER TABLE dbo.cnst_example NOCHECK CONSTRAINT fk_salary_cap;
ALTER TABLE dbo.cnst_example CHECK CONSTRAINT fk_salary_cap;
CEWII
July 1, 2009 at 3:56 pm
suresh0123456789 (7/1/2009)
Is there any way to delete records without dropping FKs
Delete the child records first.
If you want to make the fk cascade, look up ALTER TABLE in Books online. That'll give the syntax for dropping the fk and recreating with the cascade option.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 1, 2009 at 4:08 pm
Script file :
July 2, 2009 at 2:18 am
John Paul (7/1/2009)
http://www.sqlteam.com/article/performing-a-cascade-delete-in-sql-server-7
That's written for SQL 7. Sure you want to use that?
I don't get why you want a specialised proc to do it. If you're got two tables Parent and Child with a foreign key, delete the rows from Child first, then delete the rows from Parent.
CREATE Table Parent (
id int identity primary key,
SomeString varchar (50),
ToBeDeleted bit default 0
)
Create Table Child (
id int identity primary key,
ParentID int Foreign key references Parent (id),
SomeString varchar (50)
)
GO
Insert into Parent (SomeString, ToBeDeleted) Values ('a',0)
Insert into Parent (SomeString, ToBeDeleted) Values ('b',1)
Insert into Parent (SomeString, ToBeDeleted) Values ('c',0)
Insert into Child (ParentID, SomeString) values (1,'aa')
Insert into Child (ParentID, SomeString) values (1,'ab')
Insert into Child (ParentID, SomeString) values (1,'ac')
Insert into Child (ParentID, SomeString) values (2,'ba')
Insert into Child (ParentID, SomeString) values (2,'bb')
Insert into Child (ParentID, SomeString) values (3,'cc')
delete from Parent where ToBeDeleted = 1 /* fails with a fk violation*/
-- delete from the child table first
delete from Child where ParentID IN (select ID from Parent where ToBeDeleted = 1)
-- delete from the parent table after
delete from Parent where ToBeDeleted = 1
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 3, 2009 at 3:01 am
An alternative is to set the foreign key in the child table to NULL before deleting the parent.
In Gails example this would mean adding the following just before deleting the parent rows:
update child set ParentID=NULL where ParentID IN (select ID from Parent where ToBeDeleted = 1)
This would keep the child records, albeit orphaned (aahh, poor little things :-)).
It all depends on what you want to acheive.
Nigel
How to post questions
July 3, 2009 at 6:37 am
suresh0123456789 (7/1/2009)Is there any way to delete records without dropping FKs
Yes, doing it the right way -please refer to Gail's post.
I'm shocked and appalled some people have a total disregard for Referential Integrity. RF is set for one reason and one reason only, to protect the integrity of data. Why people insist in taking actions that may lead to trashing a database is something beyond my imagination. :doze: Sad.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.July 3, 2009 at 7:01 am
I'm shocked and appalled some people have a total disregard for Referential Integrity.
Can't say I'm shocked... Appalled, yes.;-)
July 3, 2009 at 7:22 am
Ian Scarlett (7/3/2009)
I'm shocked and appalled some people have a total disregard for Referential Integrity.
Can't say I'm shocked... Appalled, yes.;-)
Absolutely !!! Downright stupid 😉
Nigel
How to post questions
July 6, 2009 at 11:20 am
I'm shocked and appalled some people have a total disregard for Referential Integrity.
Sometimes a DBA's gotta do what a DBA's gotta do...
-MarkO
"You do not really understand something until you can explain it to your grandmother" - Albert Einstein
July 6, 2009 at 1:42 pm
molson (7/6/2009)
I'm shocked and appalled some people have a total disregard for Referential Integrity.
Sometimes a DBA's gotta do what a DBA's gotta do...
:blink: do you mean... trash referential integrity? 😀
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.July 6, 2009 at 1:59 pm
PaulB (7/6/2009)
molson (7/6/2009)
I'm shocked and appalled some people have a total disregard for Referential Integrity.
Sometimes a DBA's gotta do what a DBA's gotta do...
:blink: do you mean... trash referential integrity? 😀
or more correctly:
Sometimes a DBA's gotta do in a non-production environment what a DBA's gotta do in a non-production environment...
-MarkO
"You do not really understand something until you can explain it to your grandmother" - Albert Einstein
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply