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.