Deleting a row from self-referencing table
- by Jake Rutherford
Came across this the other day and thought “this would be a great interview question!” I’d created a table with a self-referencing foreign key. The application was calling a stored procedure I’d created to delete a row which caused but of course…a foreign key exception. You may say “why not just use a the cascade delete option?” Good question, easy answer. With a typical foreign key relationship between different tables which would work. However, even SQL Server cannot do a cascade delete of a row on a table with self-referencing foreign key. So, what do you do?…… In my case I re-wrote the stored procedure to take advantage of recursion: -- recursively deletes a Foo ALTER PROCEDURE [dbo].[usp_DeleteFoo] @ID int ,@Debug bit = 0 AS SET NOCOUNT ON; BEGIN TRANSACTION BEGIN TRY DECLARE @ChildFoos TABLE ( ID int ) DECLARE @ChildFooID int INSERT INTO @ChildFoos SELECT ID FROM Foo WHERE ParentFooID = @ID WHILE EXISTS (SELECT ID FROM @ChildFoos) BEGIN SELECT TOP 1 @ChildFooID = ID FROM @ChildFoos DELETE FROM @ChildFoos WHERE ID = @ChildFooID EXEC usp_DeleteFoo @ChildFooID END DELETE FROM dbo.[Foo] WHERE [ID] = @ID IF @Debug = 1 PRINT 'DEBUG:usp_DeleteFoo, deleted - ID: ' + CONVERT(VARCHAR, @ID) COMMIT TRANSACTION END TRY BEGIN CATCH ROLLBACK TRANSACTION DECLARE @ErrorMessage VARCHAR(4000), @ErrorSeverity INT, @ErrorState INT SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE() IF @ErrorState <= 0 SET @ErrorState = 1 INSERT INTO ErrorLog(ErrorNumber,ErrorSeverity,ErrorState,ErrorProcedure,ErrorLine,ErrorMessage) VALUES(ERROR_NUMBER(), @ErrorSeverity, @ErrorState, ERROR_PROCEDURE(), ERROR_LINE(), @ErrorMessage) RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState) END CATCH This procedure will first determine any rows which have the row we wish to delete as it’s parent. It then simply iterates each child row calling the procedure recursively in order to delete all ancestors before eventually deleting the row we wish to delete.
