Deshabilitar temporalmente todas las restricciones de clave externa

Estoy ejecutando un paquete SSIS que reemplazará los datos de algunas tablas de FlatFiles a tablas existentes en una base de datos.

Mi paquete truncará las tablas y luego insertará los nuevos datos. Cuando ejecuto mi paquete SSIS, recibo una excepción debido a las claves externas.

¿Puedo desactivar las restricciones, ejecutar mi importación y luego volver a habilitarlas?

Para deshabilitar restricciones de clave externa:

 DECLARE @sql NVARCHAR(MAX) = N''; ;WITH x AS ( SELECT DISTINCT obj = QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id)) + '.' + QUOTENAME(OBJECT_NAME(parent_object_id)) FROM sys.foreign_keys ) SELECT @sql += N'ALTER TABLE ' + obj + ' NOCHECK CONSTRAINT ALL; ' FROM x; EXEC sp_executesql @sql; 

Para volver a habilitar:

 DECLARE @sql NVARCHAR(MAX) = N''; ;WITH x AS ( SELECT DISTINCT obj = QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id)) + '.' + QUOTENAME(OBJECT_NAME(parent_object_id)) FROM sys.foreign_keys ) SELECT @sql += N'ALTER TABLE ' + obj + ' WITH CHECK CHECK CONSTRAINT ALL; ' FROM x; EXEC sp_executesql @sql; 

Sin embargo, no podrá truncar las tablas, deberá eliminarlas en el orden correcto. Si necesita truncarlos , debe eliminar las restricciones por completo y volver a crearlas. Esto es simple de hacer si sus restricciones de clave externa son simples restricciones de una sola columna, pero definitivamente más complejas si hay varias columnas involucradas.

Aquí hay algo que puedes probar. Para que esto sea parte de su paquete de SSIS, necesitará un lugar donde almacenar las definiciones de FK mientras se ejecuta el paquete de SSIS (no podrá hacer esto en un solo script). Entonces, en alguna base de datos de utilidad, crea una tabla:

 CREATE TABLE dbo.PostCommand(cmd NVARCHAR(MAX)); 

Luego, en su base de datos, puede tener un procedimiento almacenado que hace esto:

 DELETE other_database.dbo.PostCommand; DECLARE @sql NVARCHAR(MAX) = N''; SELECT @sql += N'ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(fk.parent_object_id)) + '.' + QUOTENAME(OBJECT_NAME(fk.parent_object_id)) + ' ADD CONSTRAINT ' + fk.name + ' FOREIGN KEY (' + STUFF((SELECT ',' + c.name FROM sys.columns AS c INNER JOIN sys.foreign_key_columns AS fkc ON fkc.parent_column_id = c.column_id AND fkc.parent_object_id = c.[object_id] WHERE fkc.constraint_object_id = fk.[object_id] ORDER BY fkc.constraint_column_id FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 1, '') + ') REFERENCES ' + QUOTENAME(OBJECT_SCHEMA_NAME(fk.referenced_object_id)) + '.' + QUOTENAME(OBJECT_NAME(fk.referenced_object_id)) + '(' + STUFF((SELECT ',' + c.name FROM sys.columns AS c INNER JOIN sys.foreign_key_columns AS fkc ON fkc.referenced_column_id = c.column_id AND fkc.referenced_object_id = c.[object_id] WHERE fkc.constraint_object_id = fk.[object_id] ORDER BY fkc.constraint_column_id FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 1, '') + '); ' FROM sys.foreign_keys AS fk WHERE OBJECTPROPERTY(parent_object_id, 'IsMsShipped') = 0; INSERT other_database.dbo.PostCommand(cmd) SELECT @sql; IF @@ROWCOUNT = 1 BEGIN SET @sql = N''; SELECT @sql += N'ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(fk.parent_object_id)) + '.' + QUOTENAME(OBJECT_NAME(fk.parent_object_id)) + ' DROP CONSTRAINT ' + fk.name + '; ' FROM sys.foreign_keys AS fk; EXEC sp_executesql @sql; END 

Ahora cuando su paquete de SSIS haya terminado, debería llamar a un procedimiento almacenado diferente, que hace:

 DECLARE @sql NVARCHAR(MAX); SELECT @sql = cmd FROM other_database.dbo.PostCommand; EXEC sp_executesql @sql; 

Si está haciendo todo esto solo por el hecho de poder truncar en lugar de eliminar, le sugiero que solo tome el hit y ejecute un delete. Tal vez use un modelo de recuperación de registro masivo para minimizar el impacto del registro. En general, no veo cómo esta solución será mucho más rápida que solo usar una eliminación en el orden correcto.

En 2014 publiqué una publicación más elaborada sobre esto aquí:

  • Suelte y vuelva a crear todas las restricciones de clave externa en SQL Server

Use el procedimiento almacenado sp_msforeachtable incorporado .

Para deshabilitar todas las restricciones:

 EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT ALL"; 

Para habilitar todas las restricciones:

 EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL"; 

Para soltar todas las tablas:

 EXEC sp_msforeachtable "DROP TABLE ?"; 

Se proporciona una buena referencia en: http://msdn.microsoft.com/en-us/magazine/cc163442.aspx en la sección “Deshabilitar todas las claves externas”

Inspirado en él, se puede hacer un acercamiento creando una tabla temporal e insertando las restricciones en esa tabla, y luego descartando las restricciones y luego volviéndolas a aplicar desde esa tabla temporal. Suficiente dijo aquí es de lo que estoy hablando

  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 

Deshabilitar todas las restricciones de la tabla

 ALTER TABLE TableName NOCHECK CONSTRAINT ConstraintName 

– Habilitar todas las restricciones de la tabla

 ALTER TABLE TableName CHECK CONSTRAINT ConstraintName 

no es necesario ejecutar consultas a FK sidable en sql. Si tiene un FK de la tabla A a la B, debe:

  • eliminar datos de la tabla A
  • eliminar datos de la tabla B
  • insertar datos en B
  • insertar datos en A

También puedes decirle al destino que no verifique las restricciones

enter image description here

Truncar la tabla no será posible aunque deshabilite las claves externas. También puede usar el comando Eliminar para eliminar todos los registros de la tabla, pero tenga en cuenta que si usa el comando eliminar para una tabla que consta de millones de registros, entonces su paquete será lento y su tamaño de registro de transacciones boostá y puede llenar su valioso espacio en disco.

Si elimina las restricciones, puede suceder que llene su tabla con datos sucios y cuando intente recrear las restricciones, es posible que no le permita, ya que dará errores. así que asegúrese de que si elimina las restricciones, está cargando datos que están correctamente relacionados entre sí y que satisfacen las relaciones de restricción que va a recrear.

así que piense cuidadosamente los pros y los contras de cada método y úselos de acuerdo con sus requisitos

Desactive todos los índices (incluido el pk, que deshabilitará todos los fks), luego vuelva a activar los 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 

[Haz tu carga de datos]

Luego, haz que todo vuelva a la vida …

 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 
    Intereting Posts