Ignore foreign key constraints in batch insert

Question

I want the package to insert a large amount of generated data that has a cyclic dependency (the column in each table is a foreign key limited to another table). To get around this, I just want to disable foreign key constraints, insert data, and enable constraints again.

Google, I found many solutions, but none of them worked. Right now I have:

ALTER TABLE TableName NOCHECK CONSTRAINT ALL 

The command starts and does not produce any errors, but when I try to clear the table in preparation for inserting 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 in another table, which depends on the table being modified.

There are two solutions that I can solve with this problem:

• Go through all the table dependencies on the table into which I insert and disable foreign key constraints. It seems overly complicated.

• Disable foreign key restrictions for all tables in the database.

Any solution will work, but I'm not sure where to start in any solution. Any ideas?

Answer 1

This is what I used for this kind of 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' 

Answer 2

You can also enable ON DELETE CASCADE in FK constraints, which will delete their records whenever the PK on your main table is deleted. This will be a one-time change and will not require reloading each load.

EDIT:

For more details, here is the link to the script from the Pinal Dave (SQLAuthority) blog , which lists all the FK restrictions. The WHERE below allows you to restrict it to a specific set of PK and FK tables, if necessary.

Answer 3

Disabling Constraints and Triggers

See 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