CODEWITHSUNDEEP
mysql
ranah
ranah

Reputation: 727

Deleting a row with a foreign key constraint

I have a table of instructors with a column FileId that corresponds to a file in the Files table. When he/she replaces a file I want to be able to go and delete the old one. I DO NOT want to delete the Instructor entry, just the file. When I edited the relationship so that it would allow cascades on update and delete and then I ran the stored proc below it deleted the Instructor entry, which is not the desired effect, please let me know if you have any suggestions. Thanks for your help

   ALTER PROC [dbo].[Files_DeleteByInstructorFileId]
   @Id int,
   @FileId int = NULL

   as 
   begin

   /*

   EXECUTE [dbo].[Files_DeleteByInstructorFileId] 162

   */

    Update [dbo].[Instructors] SET 

        FileId = @FileId
       WHERE @Id = Id

       DELETE f FROM [dbo].[Files] f
        JOIN [dbo].[Instructors] ins
        ON ins.FileId = f.Id

       WHERE ins.Id = @Id


   END

Upvotes: 0

Views: 71

Answers (2)

ranah
ranah

Reputation: 727

I found a solution: I first declared a variable equal to the Instructor's fileId, then I set the fileId to null so that I could delete the file and not be restricted by the foreign key constraint, then I used the variable to find the correct file to delete. If there's other ways of doing this I'd still be interested...

DECLARE @FileIdNumber int
SELECT @FileIdNumber = [dbo].[Instructors].[FileId]
FROM [dbo].[Instructors]
WHERE [dbo].[Instructors].[Id] = @Id

Update [dbo].[Instructors] SET 

FileId = @FileId
WHERE @Id = Id

DELETE f FROM [dbo].[Files] f
WHERE f.Id = @FileIdNumber

Upvotes: 1

Lawakush Kurmi
Lawakush Kurmi

Reputation: 2996

Please make sure your referenced column(foreign key constraint) is set as CASCADE on delete and update.
like

CONSTRAINT `job_status_ibfk_1` FOREIGN KEY (`jobseeker_id`)
 REFERENCES `jobseeker` (`jobseeker_id`) ON DELETE CASCADE ON UPDATE CASCADE

Upvotes: 2

Related Questions