Deleting parent and child records

Question

I am trying to delete parent and child records from the db using the following stored procedure:

CREATE PROCEDURE [dbo].[DeleteCompanyPersonAndPhoneNumbers] (
    @personId int,
    @deleted bit output)
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    set @deleted = 0

    begin transaction

        -- delete the phone numbers
        delete from PersonalPhoneNumber 
            where PersonalPhoneNumber.PersonId = @personId

        delete from ProfessionalProfile
            where ProfessionalProfile.Person_Id = @personId  

        delete from aspnet_UserProfile
            where aspnet_UserProfile.Person_ID = @personId

        delete from Accreditation
            where Accreditation.Person_ID = @personId

        delete from Qualification
                where Qualification.Person_ID = @personId

        delete from PERSON where Person_ID = @personId

        set @deleted = 1

    commit transaction

END

I have set the foreign key enforce off. When run the sp it only delete the parent record.

I am doing anything wrong in the SP?

Thanks

Seems to work to delete children too; http://sqlfiddle.com/#!6/55fd5/1 — Joachim Isaksson, Aug 01 '13 at 14:54
your approach is the equivalent of #2 from this question : http://stackoverflow.com/questions/17855349/sql-server-recursive-delete it looks like your code should work. It really removes a row matching personId from PERSON but not from the children? The personId's definitely match up? — Graham Griffiths, Aug 01 '13 at 15:19
I need to see the Tables that you have to give you exact code. I do know you have to start with last table and work your way up the ladder to remove all children and parent. — JayKlassen, Aug 01 '13 at 14:50

score 3 · Answer 1 · answered Aug 01 '13 at 14:47

3

You have to set foreign key enforce on to delete the records in child table through on Cascade delete. Also disabling a foreign key is bad database design and could get you into trouble.

answered Aug 01 '13 at 14:47

Sonam

3,406
1
12
24

this is the best approach so far. – SDReyes Aug 01 '13 at 14:52
Though I would suggest you to either turn the foreign key enforce on or create a trigger on delete event of parent table to delete the records from the child table. But if you want to stick with the same approach then please confirm what value is getting passed through PersonId parameter, please try to include a Print statement in the procedure to confirm if correct value is being passed into PersonId parameter. – Sonam Aug 01 '13 at 14:59

Deleting parent and child records

1 Answers1