Temporarily disable all foreign key constraints

Question

I am running an SSIS package which will replace data for a few tables from FlatFiles to existing tables in a database.

My package will truncate the tables and then insert the new data. When I run my SSIS package, I get an exception because of the foreign keys.

Can I disable the constraints, run my import, then re-enable them?

Answer 1

not need to run queries to sidable FKs on sql. If you have a FK from table A to B, you should:

• delete data from table A
• delete data from table B
• insert data on B
• insert data on A

You can also tell the destination not to check constraints

Answer 2

Disable all indexes (including the pk, which will disable all fks), then reenable the pks.

DECLARE @sql AS NVARCHAR(max)=''
select @sql = @sql +
    'ALTER INDEX ALL ON [' + t.[name] + '] DISABLE;'+CHAR(13)
from  
    sys.tables t
where type='u'

select @sql = @sql +
    'ALTER INDEX ' + i.[name] + ' ON [' + t.[name] + '] REBUILD;'+CHAR(13)
from  
    sys.key_constraints i
join
    sys.tables t on i.parent_object_id=t.object_id
where
    i.type='PK'


exec dbo.sp_executesql @sql;
go

[Do your data load]

Then bring everything back to life...

DECLARE @sql AS NVARCHAR(max)=''
select @sql = @sql +
    'ALTER INDEX ALL ON [' + t.[name] + '] REBUILD;'+CHAR(13)
from  
    sys.tables t
where type='u'

exec dbo.sp_executesql @sql;
go