In the last part (Part 1) we looked at a reasonably generic script for deleting large amounts of data from SQL Server that helps when you issue a delete statement and SQL Server just sits there with your transaction logs filling up and refusing to clear out.

This time, we’re going to look at how to deal with that wonderful error message of:

Msg 547, Level 16, State 0, Line 1
The DELETE statement conflicted with the REFERENCE constraint "FK__address__personi__239E4DCF". The conflict occurred in database "dbgrow", table "dbo.address", column 'personid'.
The statement has been terminated.

Which is SQL Server’s way of letting you know that the data you’re trying to delete is referenced as part of a Foreign Key relationship, so can’t be deleted unless you delete the record on the other end of the relationship.

So how do you work around this?

The first thing you DON’T want to do is to try to work around it by disabling the foreign key constraint. Yes, there are scripts on the Interwebs that’ll remove all foreign key constraints from a databases. The problem is that there are very few that will reliably put them back afterwards!

Even more importantly, there are very good reasons for having foreign key constraints, which is to stop you deleting or modifying records in such a way that you have erroneous values or hanging references (ie; an address for a contact that no longer exists). Removing these means that your delete operation could very easily leave you with bad data or hanging references.

So building on from the example in the last post:

declare @RowsToDelete integer, @RowCount integer, @RowsToIgnore integer;
declare @Stop bit;
declare @DeletedTable table (UniqueID uniqueidentifier not null primary key);

--Size of batch
select @RowsToDelete = 10000;

--This allows us to say how many rows to ignore. Sometimes rows are added faster than you can delete them, and this is to prevent constantly looping
Select @RowsToIgnore = 5;

--This controls if we continue with the deletions
select @Stop = 0;

while (@stop = 0)
begin
    begin transaction
    insert into @DeletedTable select top (@RowsToDelete) Table1UniqueID from Table1 where DateAdded<getdate()-90;
    select @RowCount=count(1) from @DeleteTable;
    select @stop = case when @Rowcount<=@RowsToIgnore then 1 else 0 end;
    if (@stop = 0)
    begin
        delete Table1 from Table1 T1 inner join @DeletedTable dt on T1.Table1UniqueID=dt.UniqueID;
    end
    delete from @DeletedTable;
    commit transaction
end

we’re just going to add some extra delete statements to deal with those pesky foreign keys and constraints:

declare @RowsToDelete integer, @RowCount integer, @RowsToIgnore integer;
declare @Stop bit;
declare @DeletedTable table (UniqueID uniqueidentifier not null primary key);

--Size of batch
select @RowsToDelete = 10000;

--This allows us to say how many rows to ignore. Sometimes rows are added faster than you can delete them, and this is to prevent constantly looping
Select @RowsToIgnore = 5;

--This controls if we continue with the deletions
select @Stop = 0;

while (@stop = 0)
begin
    begin transaction
    insert into @DeletedTable select top (@RowsToDelete) Table1UniqueID from Table1 where DateAdded<getdate()-90;
    select @RowCount=count(1) from @DeleteTable;
    select @stop = case when @Rowcount<=@RowsToIgnore then 1 else 0 end;
    if (@stop = 0)
    begin
        delete Table2_withFK from Table2_withFK T2 inner join Table1 T1 on T2.KeyID=T1.KeyID
        delete Table3_withFK from Table3_withFK T3 inner join Table1 T1 on T3.Key2ID=T1.Key2ID
        delete Table1 from Table1 T1 inner join @DeletedTable dt on T1.Table1UniqueID=dt.UniqueID;
    end
    delete from @DeletedTable;
    commit transaction
end

This can go on for as many Foreign keys as you need. I’ve dealt with Dynamics CRM setups that have needed 8 delete statements before the main one!

And all the other modifications we discussed in part 1 can also be added