6
votes

I want to batch insert a large amount of generated data which has a circular dependency (a column in each table is foreign key constrained to the other table). To get around this, I want to just turn off the foreign key constraints, insert the data, and then turn the constraints back on.

Googling around, I found a bunch of solutions, but none of them worked. Right now I have:

ALTER TABLE TableName NOCHECK CONSTRAINT ALL

The command runs and doesn't produce any errors, but when I attempt to clear the table in preparation for inserting the data, I get the following error:

System.Data:0:in `OnError': The DELETE statement conflicted with the REFERENCE constraint "FK_1_2_ConstraintName". The conflict occurred in database "DatabaseName", table "dbo.SomeOtherTable", column 'PrimaryKey'.\r\nThe statement has been terminated.\r\nChecking identity information: current identity value '0', current column value '0'.\r\nDBCC execution completed. If DBCC printed error messages, contact your system administrator. (System::Data::SqlClient::SqlException)

My current theory is that this is caused by a foreign key constraint on the other table which depends on the table being changed.

There are two solutions I can come up with to this problem:

  1. Go through all the tables with dependencies on the table I'm inserting into and disable their foreign key constraints. This seems unnecessarily complicated.

  2. Disable foreign key constraints on all of the tables in the database.

Either solution would work, but I'm not sure where to start on either solution. Any ideas?

3
I'd be pretty concerned if I had a circular dependency between two tables. That generally means you havea serious design issue.HLGEM
@HLGEM Not my design and I don't have the option to change it. :|kerkeslager

3 Answers

12
votes

This is what I used for this kind work.

--Disable all Constraints 
exec sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL' 

-- INSERT DATA HERE

--Enable all Constraints 
exec sp_MSforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL' 
1
votes

You could also enable ON DELETE CASCADE on the FK Constraints, which will cause their records to be deleted whenever the PK on your main table is deleted. This would be a one-time change and wouldn't require you to rerun it each load.

EDIT:

More info, here is a link to a script from Pinal Dave's blog (SQLAuthority) that lists all FK constraints. The WHERE clause at the bottom lets you restrict it to a certain PK and FK table set if desired.

1
votes

Disabling Constraints and Triggers

See the section "Disabling All Foreign Keys"

CREATE PROCEDURE pr_Disable_Triggers_v2 
    @disable BIT = 1
AS 
    DECLARE
        @sql VARCHAR(500),
        @tableName VARCHAR(128),
        @tableSchema VARCHAR(128)

    -- List of all tables
    DECLARE triggerCursor CURSOR
        FOR
    SELECT
        t.TABLE_NAME AS TableName,
        t.TABLE_SCHEMA AS TableSchema
    FROM
        INFORMATION_SCHEMA.TABLES t
    ORDER BY
        t.TABLE_NAME,
        t.TABLE_SCHEMA 

    OPEN triggerCursor

    FETCH NEXT FROM triggerCursor 
    INTO @tableName, @tableSchema

    WHILE ( @@FETCH_STATUS = 0 )
        BEGIN
            IF @disable = 1 
                SET @sql = ‘ALTER TABLE ‘ + @tableSchema 
                    + ‘.[‘ + @tableName + ‘] DISABLE TRIGGER ALL’ 
            ELSE 
                SET @sql = ‘ALTER TABLE ‘ + @tableSchema 
                    + ‘.[‘ + @tableName + ‘] ENABLE TRIGGER ALL’ 

            PRINT ‘Executing Statement - ‘ + @sql

            EXECUTE ( @sql )
            FETCH NEXT FROM triggerCursor
            INTO @tableName, @tableSchema
        END

    CLOSE triggerCursor
    DEALLOCATE triggerCursor