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?
This question is related to
sql-server
sql-server-2008
ssis
constraints
Disable all table constraints
ALTER TABLE TableName NOCHECK CONSTRAINT ConstraintName
-- Enable all table constraints
ALTER TABLE TableName CHECK CONSTRAINT ConstraintName
not need to run queries to sidable FKs on sql. If you have a FK from table A to B, you should:
You can also tell the destination not to check constraints
In case you use a different database schemas than ".dbo" or your db is containing Pk´s, which are composed by several fields, please don´t use the the solution of Carter Medlin, otherwise you will damage your db!!!
When you are working with different schemas try this (don´t forget to make a backup of your database before!):
DECLARE @sql AS NVARCHAR(max)=''
select @sql = @sql +
'ALTER INDEX ALL ON ' + SCHEMA_NAME( t.schema_id) +'.'+ '['+ t.[name] + '] DISABLE;'+CHAR(13)
from
sys.tables t
where type='u'
select @sql = @sql +
'ALTER INDEX ' + i.[name] + ' ON ' + SCHEMA_NAME( t.schema_id) +'.'+'[' + 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
After doing some Fk-free actions, you can switch back with
DECLARE @sql AS NVARCHAR(max)=''
select @sql = @sql +
'ALTER INDEX ALL ON ' + SCHEMA_NAME( t.schema_id) +'.'+'[' + t.[name] + '] REBUILD;'+CHAR(13)
from
sys.tables t
where type='u'
print @sql
exec dbo.sp_executesql @sql;
exec sp_msforeachtable "ALTER TABLE ? WITH NOCHECK CHECK CONSTRAINT ALL";
Truncating the table wont be possible even if you disable the foreign keys.so you can use delete command to remove all the records from the table,but be aware if you are using delete command for a table which consists of millions of records then your package will be slow and your transaction log size will increase and it may fill up your valuable disk space.
If you drop the constraints it may happen that you will fill up your table with unclean data and when you try to recreate the constraints it may not allow you to as it will give errors. so make sure that if you drop the constraints,you are loading data which are correctly related to each other and satisfy the constraint relations which you are going to recreate.
so please carefully think the pros and cons of each method and use it according to your requirements
A good reference is given at : http://msdn.microsoft.com/en-us/magazine/cc163442.aspx under the section "Disabling All Foreign Keys"
Inspired from it, an approach can be made by creating a temporary table and inserting the constraints in that table, and then dropping the constraints and then reapplying them from that temporary table. Enough said here is what i am talking about
SET NOCOUNT ON
DECLARE @temptable TABLE(
Id INT PRIMARY KEY IDENTITY(1, 1),
FKConstraintName VARCHAR(255),
FKConstraintTableSchema VARCHAR(255),
FKConstraintTableName VARCHAR(255),
FKConstraintColumnName VARCHAR(255),
PKConstraintName VARCHAR(255),
PKConstraintTableSchema VARCHAR(255),
PKConstraintTableName VARCHAR(255),
PKConstraintColumnName VARCHAR(255)
)
INSERT INTO @temptable(FKConstraintName, FKConstraintTableSchema, FKConstraintTableName, FKConstraintColumnName)
SELECT
KeyColumnUsage.CONSTRAINT_NAME,
KeyColumnUsage.TABLE_SCHEMA,
KeyColumnUsage.TABLE_NAME,
KeyColumnUsage.COLUMN_NAME
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE KeyColumnUsage
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TableConstraints
ON KeyColumnUsage.CONSTRAINT_NAME = TableConstraints.CONSTRAINT_NAME
WHERE
TableConstraints.CONSTRAINT_TYPE = 'FOREIGN KEY'
UPDATE @temptable SET
PKConstraintName = UNIQUE_CONSTRAINT_NAME
FROM
@temptable tt
INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS ReferentialConstraint
ON tt.FKConstraintName = ReferentialConstraint.CONSTRAINT_NAME
UPDATE @temptable SET
PKConstraintTableSchema = TABLE_SCHEMA,
PKConstraintTableName = TABLE_NAME
FROM @temptable tt
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TableConstraints
ON tt.PKConstraintName = TableConstraints.CONSTRAINT_NAME
UPDATE @temptable SET
PKConstraintColumnName = COLUMN_NAME
FROM @temptable tt
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KeyColumnUsage
ON tt.PKConstraintName = KeyColumnUsage.CONSTRAINT_NAME
--Now to drop constraint:
SELECT
'
ALTER TABLE [' + FKConstraintTableSchema + '].[' + FKConstraintTableName + ']
DROP CONSTRAINT ' + FKConstraintName + '
GO'
FROM
@temptable
--Finally to add constraint:
SELECT
'
ALTER TABLE [' + FKConstraintTableSchema + '].[' + FKConstraintTableName + ']
ADD CONSTRAINT ' + FKConstraintName + ' FOREIGN KEY(' + FKConstraintColumnName + ') REFERENCES [' + PKConstraintTableSchema + '].[' + PKConstraintTableName + '](' + PKConstraintColumnName + ')
GO'
FROM
@temptable
GO
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
Use the built-in sp_msforeachtable stored procedure.
To disable all constraints:
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT ALL";
To enable all constraints:
EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL";
To drop all the tables:
EXEC sp_msforeachtable "DROP TABLE ?";
There is a easy way to this.
-- Disable all the constraint in database
EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all'
-- Enable all the constraint in database
EXEC sp_msforeachtable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all'
Source: Stackoverflow.com