May 3, 2011 at 7:24 pm
hi
i want to delete all the records from 2 tables ,that r link with pk->fk
for ex, emp : empid ,name
1 , joe
2 john
3 sandy
and emp1 : empid salary
1 2000
2 3456
3 4567
now i want to delete all the records of john from 2 tables at the same time
as it is pk->fk i am not able to delele, with single delte statement
Any suggestion would be appreciate.
thanks
May 3, 2011 at 7:43 pm
Hi
You should check ON DELETE CASCADE option.
Please check BOL:
ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_1devconc/html/54ca1d10-5106-4340-bde4-480b83e7e813.htm
May 3, 2011 at 8:17 pm
hi
ON DELETE CASCADE is applied while table creating ,here table is already created
i cant change them
May 3, 2011 at 9:17 pm
daveriya (5/3/2011)
hiON DELETE CASCADE is applied while table creating ,here table is already created
i cant change them
Can't or not allowed? Cause I'm pretty sure you can change that setting after the table is created.
May 3, 2011 at 10:31 pm
Need to run ALTER TABLE
Please check BOL for syntax, you can still add ON DELETE CASCADE even if the table is already created.
May 3, 2011 at 10:52 pm
You could also write a trigger yourself,
something like:
CREATE TRIGGER trgDel_DeleteChildren
ON ParentTable
FOR DELETE
AS
delete from ChildTable
where id in (select id from deleted)
Id would be refference value between those two tables
'deleted' is virtual table containing deleted records from parent table
May 4, 2011 at 1:32 am
daveriya (5/3/2011)
hii want to delete all the records from 2 tables ,that r link with pk->fk
for ex, emp : empid ,name
1 , joe
2 john
3 sandy
and emp1 : empid salary
1 2000
2 3456
3 4567
now i want to delete all the records of john from 2 tables at the same time
as it is pk->fk i am not able to delele, with single delte statement
Any suggestion would be appreciate.
thanks
If its a adhoc/specifc user's request for deletion of records then its fine to have deletion of child table and then parent ( if required) otherwise OP can have FK "delete on cascade" or "delete dml trigger".
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply