Distriggers temporaneamente tutti i vincoli di chiave esterna

Sto facendo funzionare un pacchetto di SSIS che sostituirà i dati per alcune tabelle da FlatFiles alle tabelle esistenti in una base di dati.

Il mio pacchetto troncherà le tabelle e quindi inserirà i nuovi dati. Quando eseguo il mio pacchetto SSIS, ottengo un’eccezione a causa delle chiavi esterne.

Posso disabilitare i vincoli, eseguire la mia importazione, quindi ritriggersrli?

Per disabilitare i vincoli di chiave esterna:

 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; 

Per ritriggersre:

 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; 

Tuttavia, non sarà ansible troncare i tavoli, sarà necessario eliminarli nell’ordine corretto. Se è necessario troncarli , è necessario eliminare completamente i vincoli e ricrearli. Questo è semplice da fare se i vincoli delle chiavi esterne sono tutti semplici, vincoli a colonna singola, ma sicuramente più complessi se sono coinvolte più colonne.

Ecco qualcosa che puoi provare. Per rendere questo una parte del tuo pacchetto SSIS avrai bisogno di un posto dove memorizzare le definizioni FK mentre il pacchetto SSIS è in esecuzione (non sarai in grado di farlo tutto in uno script). Quindi, in qualche database di utilità, crea una tabella:

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

Quindi nel tuo database, puoi avere una procedura memorizzata che fa questo:

 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 

Ora, quando il tuo pacchetto SSIS è finito, dovrebbe chiamare una stored procedure diversa, che:

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

Se stai facendo tutto questo solo per essere in grado di troncare invece di eliminare, ti suggerisco di prendere il colpo e di eseguire un’eliminazione. Forse utilizzare il modello di recupero con registrazione di massa per ridurre al minimo l’impatto del log. In generale, non vedo come questa soluzione sarà molto più veloce di una semplice eliminazione nell’ordine corretto.

Nel 2014 ho pubblicato un post più elaborato su questo qui:

  • Eliminare e ricreare tutti i vincoli di chiave esterna in SQL Server

Utilizzare la procedura memorizzata sp_msforeachtable integrata .

Per disabilitare tutti i vincoli:

 EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT ALL"; 

Per abilitare tutti i vincoli:

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

Per eliminare tutti i tavoli:

 EXEC sp_msforeachtable "DROP TABLE ?"; 

Un buon riferimento è dato a: http://msdn.microsoft.com/en-us/magazine/cc163442.aspx nella sezione “Disabilitazione di tutte le chiavi esterne”

Ispirato da questo, un approccio può essere fatto creando una tabella temporanea e inserendo i vincoli in quella tabella, quindi rilasciando i vincoli e riapplicandoli da quella tabella temporanea. Basta dire qui è di cosa sto parlando

  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 

Disabilita tutti i vincoli di tabella

 ALTER TABLE TableName NOCHECK CONSTRAINT ConstraintName 

– Abilita tutti i vincoli di tabella

 ALTER TABLE TableName CHECK CONSTRAINT ConstraintName 

non è necessario eseguire query su FK sidable su sql. Se hai un FK dalla tabella A alla B, dovresti:

  • elimina i dati dalla tabella A
  • cancella i dati dalla tabella B
  • inserire i dati su B
  • inserire i dati su A

Puoi anche dire alla destinazione di non controllare i vincoli

inserisci la descrizione dell'immagine qui

Non è ansible troncare la tabella anche se si disabilitano le chiavi esterne. In questo modo è ansible utilizzare il comando delete per rimuovere tutti i record dalla tabella, ma tenere presente che si sta utilizzando il comando delete per una tabella composta da milioni di record, quindi il pacchetto sarà lento e la dimensione del registro delle transazioni aumenterà e potrebbe riempire il tuo prezioso spazio su disco.

Se si rilasciano i vincoli, può accadere che si riempia la tabella con dati non corretti e quando si tenta di ricreare i vincoli che potrebbe non consentirvi poiché genererebbero errori. quindi assicurati che se lasci cadere i vincoli, stai caricando dati che sono correttamente correlati tra loro e che soddisfano le relazioni di vincoli che stai per ricreare.

quindi per favore pensa attentamente i pro e i contro di ogni metodo e usalo secondo le tue esigenze

Disabilita tutti gli indici (incluso il pk, che disabiliterà tutti i file), quindi ritriggersre i 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 

[Caricare i dati]

Quindi riporta tutto alla vita …

 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