Is there a way to test whether DELETE will fail due to constraints?

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?

-
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
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

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

-

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

-
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
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
@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

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

-