Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'd like to be able to predict whether a DELETE will run into a constraint violation, without actually performing the delete.

What are my options for doing this? Is there a simple way to do a "dry run" of a DELETE?

share|improve this question
    
Are you trying to prevent the exception for this statement alone, or are you trying to ease error handling in a larger batch that contains this delete? –  Aaron Bertrand Aug 23 '12 at 20:52
3  
Could you check to see if a FK exists, and run a SELECT statement to check the values? –  SQLRockstar Aug 23 '12 at 21:02
    
Aaron: We need to run a batch of several DELETEs in separate transactions. If one fails, the others are already committed. (Bad design from the start, I know, but it's not my application, and it's not changing.) The best workaround at the moment sounds like doing a dry check to see if the DELETEs will fail. –  notfed Aug 23 '12 at 21:05
    
Still not sure I understand. Are you trying to let the rest of the deletes succeed, or are you trying to check up front that all of the deletes will succeed, or none of them should? –  Aaron Bertrand Aug 23 '12 at 21:22
    
Aaron: Sorry I didn't make it clear, but yes, I'm trying to make sure they all succeed, or none of them succeed. –  notfed Aug 23 '12 at 21:38
add comment

3 Answers 3

up vote 12 down vote accepted

If your goal is to process all deletes only if they all succeed, why not just use TRY/CATCH:

BEGIN TRANSACTION;
BEGIN TRY
  DELETE #1;
  DELETE #2;
  DELETE #3;
  COMMIT TRANSACTION;
END TRY
BEGIN CATCH
  ROLLBACK TRANSACTION;
END CATCH

If the goal is to allow all successful deletes to succeed even if one or more will fail, then you can use individual TRY/CATCH, e.g.

BEGIN TRY
  DELETE #1;
END TRY
BEGIN CATCH
  PRINT 1;
END CATCH

BEGIN TRY
  DELETE #2;
END TRY
BEGIN CATCH
  PRINT 1;
END CATCH
share|improve this answer
add comment

One option is to begin a transaction, run your delete, and then always rollback:

begin tran

delete Table1 where col1 = 1

-- Test whether it is there
select * from Table1 where col1 = 1

rollback tran

-- Confirm that it is still there
select * from Table1 where col1 = 1
share|improve this answer
1  
And if the delete succeeds, run it again? What if the delete is very expensive? And if the delete fails, what then? You've done one delete and two selects. How do I decide to move on the next delete or not? –  Aaron Bertrand Aug 23 '12 at 21:52
1  
If those are part of the requirements, then they should be handled. This answer is in regard to a "simple 'dry run'". –  GaTechThomas Aug 23 '12 at 22:27
    
Well they weren't when you first submitted your answer, but they have been clarified now. –  Aaron Bertrand Aug 23 '12 at 22:28
4  
@GaTechThomas Aaron contributes a lot, so he is sometimes brief, but I am certain his intention was not to be aggressive. I discussed this in The Heap and I'd be grateful for the chance to do so with you as well. –  Jack Douglas Aug 24 '12 at 14:50
    
@JackDouglas I have read The Heap comments that you reference and understand the point. The community comments were reasonable except for the part where I was called a clown for pointing out his aggression. I don't understand how I am the one who was seen as aggressive. I posted a legitimate answer to the question as it was posed at the time. It did not ask for production quality - sometimes you just need quick and easy. So on my answer I get pointed questions. The appearance was that he was denigrating my answer so that his would be picked. Should we take this thread elsewhere? –  GaTechThomas Sep 11 '12 at 16:59
add comment

I would like to improve the solution provided by Aaron Bertrand with some code, in case you want to try to add any element of a table, managing the exceptions to ignore fails or also stop the process afters errors.

This one will select the records from the table and then tries to delete them without exceptions:

DECLARE @MaxErrors INT
SET @MaxErrors = 5;    // Setting 0 will stop process after the first error!

SELECT
    [Id]
    , ROW_NUMBER() OVER (ORDER BY Id ASC) AS [Index]
INTO #DeletingItems
FROM myTable

DECLARE @Current INT, @Max INT, @Id INT, @TotErrors INT
SELECT
    @Current = 1
    , @TotErrors = 0
    , @Max = MAX([Index])
FROM #DeletingTable

WHILE @Current <= @Max
BEGIN
    SELECT
        @Id = [Id]
    FROM #DeletingItems
    WHERE
        [Index] = @Index;

    BEGIN TRANSACTION;    
    BEGIN TRY    
        DELETE FROM myTable WHERE [Id] = @Id;

        COMMIT TRANSACTION;    
    END TRY    
    BEGIN CATCH    
        ROLLBACK TRANSACTION;

        SET @TotErrors = @TotErrors + 1;

        IF @TotErrors > @MaxErrors
            BREAK;
    END CATCH

    SET @Current = @Current + 1;
END
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.