Comment les contraintes de key étrangère peuvent-elles être temporairement désactivées en utilisant T-SQL?

Si vous souhaitez désactiver toutes les contraintes dans la database, exécutez simplement ce code:

 -- disable all constraints EXEC sp_MSforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all" 

Pour les rallumer, exécutez: (l'printing est facultative bien sûr et il s'agit juste d'énumérer les arrays)

 -- enable all constraints exec sp_MSforeachtable @command1="print '?'", @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all" 

Je trouve cela utile lors du remplissage de données d'une database à une autre. C'est une bien meilleure approche que de faire tomber les contraintes. Comme vous l'avez mentionné, il est utile lorsque vous supprimez toutes datatables de la database et que vous les repeuplerez (par exemple, dans un environnement de test).

Si vous supprimez toutes datatables, vous pouvez find cette solution utile.

Parfois aussi, il est utile de désactiver tous les triggersurs, vous pouvez voir la solution complète ici .

http://www.sqljunkies.com/WebLog/roman/archive/2005/01/30/7037.aspx

 -- Disable all table constraints ALTER TABLE MyTable NOCHECK CONSTRAINT ALL -- Enable all table constraints ALTER TABLE MyTable WITH CHECK CHECK CONSTRAINT ALL -- Disable single constraint ALTER TABLE MyTable NOCHECK CONSTRAINT MyConstraint -- Enable single constraint ALTER TABLE MyTable WITH CHECK CHECK CONSTRAINT MyConstraint 

Votre meilleure option consiste à DROP et CREER des contraintes de key étrangère.

Je n'ai pas trouvé d'exemples dans ce post qui fonctionneraient pour moi "tel quel", on ne fonctionnerait pas si les foreign keys référençaient des schémas différents, l'autre ne fonctionnerait pas si la key étrangère reference plusieurs colonnes. Ce script considère à la fois plusieurs schémas et plusieurs colonnes par key étrangère.

Voici le script qui génère les instructions "ADD CONSTRAINT", pour plusieurs colonnes, il les séparera par virgule ( assurez-vous de sauvegarder cette sortie avant d'exécuter les instructions DROP ):

 PRINT N'-- CREATE FOREIGN KEY CONSTRAINTS --'; SET NOCOUNT ON; SELECT ' PRINT N''Creating '+ const.const_name +'...'' GO ALTER TABLE ' + const.parent_obj + ' ADD CONSTRAINT ' + const.const_name + ' FOREIGN KEY ( ' + const.parent_col_csv + ' ) REFERENCES ' + const.ref_obj + '(' + const.ref_col_csv + ') GO' FROM ( SELECT QUOTENAME(fk.NAME) AS [const_name] ,QUOTENAME(schParent.NAME) + '.' + QUOTENAME(OBJECT_name(fkc.parent_object_id)) AS [parent_obj] ,STUFF(( SELECT ',' + QUOTENAME(COL_NAME(fcP.parent_object_id, fcp.parent_column_id)) FROM sys.foreign_key_columns AS fcP WHERE fcp.constraint_object_id = fk.object_id FOR XML path('') ), 1, 1, '') AS [parent_col_csv] ,QUOTENAME(schRef.NAME) + '.' + QUOTENAME(OBJECT_NAME(fkc.referenced_object_id)) AS [ref_obj] ,STUFF(( SELECT ',' + QUOTENAME(COL_NAME(fcR.referenced_object_id, fcR.referenced_column_id)) FROM sys.foreign_key_columns AS fcR WHERE fcR.constraint_object_id = fk.object_id FOR XML path('') ), 1, 1, '') AS [ref_col_csv] FROM sys.foreign_key_columns AS fkc INNER JOIN sys.foreign_keys AS fk ON fk.object_id = fkc.constraint_object_id INNER JOIN sys.objects AS oParent ON oParent.object_id = fkc.parent_object_id INNER JOIN sys.schemas AS schParent ON schParent.schema_id = oParent.schema_id INNER JOIN sys.objects AS oRef ON oRef.object_id = fkc.referenced_object_id INNER JOIN sys.schemas AS schRef ON schRef.schema_id = oRef.schema_id GROUP BY fkc.parent_object_id ,fkc.referenced_object_id ,fk.NAME ,fk.object_id ,schParent.NAME ,schRef.NAME ) AS const ORDER BY const.const_name 

Voici le script qui génère les instructions "DROP CONSTRAINT":

 PRINT N'-- DROP FOREIGN KEY CONSTRAINTS --'; SET NOCOUNT ON; SELECT ' PRINT N''Dropping ' + fk.NAME + '...'' GO ALTER TABLE [' + sch.NAME + '].[' + OBJECT_NAME(fk.parent_object_id) + ']' + ' DROP CONSTRAINT ' + '[' + fk.NAME + '] GO' FROM sys.foreign_keys AS fk INNER JOIN sys.schemas AS sch ON sch.schema_id = fk.schema_id ORDER BY fk.NAME 

Pour désactiver la contrainte, vous avez ALTER la table en utilisant NOCHECK

 ALTER TABLE [TABLE_NAME] NOCHECK CONSTRAINT [ALL|CONSTRAINT_NAME] 

Pour vous permettre d'utiliser double CHECK

 ALTER TABLE [TABLE_NAME] WITH CHECK CHECK CONSTRAINT [ALL|CONSTRAINT_NAME] 

• Faites attention au double CHECK CHECK lors de l'activation.
• TOUT signifie pour toutes les contraintes de la table.

Une fois terminé, si vous avez besoin de vérifier l'état, utilisez ce script pour listr le statut de la contrainte. Sera très utile:

  SELECT (CASE WHEN OBJECTPROPERTY(CONSTID, 'CNSTISDISABLED') = 0 THEN 'ENABLED' ELSE 'DISABLED' END) AS STATUS, OBJECT_NAME(CONSTID) AS CONSTRAINT_NAME, OBJECT_NAME(FKEYID) AS TABLE_NAME, COL_NAME(FKEYID, FKEY) AS COLUMN_NAME, OBJECT_NAME(RKEYID) AS REFERENCED_TABLE_NAME, COL_NAME(RKEYID, RKEY) AS REFERENCED_COLUMN_NAME FROM SYSFOREIGNKEYS ORDER BY TABLE_NAME, CONSTRAINT_NAME,REFERENCED_TABLE_NAME, KEYNO 

Le standard SQL-92 permet de déclarer une contrainte comme DEFERRABLE afin qu'elle puisse être différée (implicitement ou explicitement) dans le cadre d'une transaction. Malheureusement, SQL Server n'a toujours pas cette fonctionnalité SQL-92.

Pour moi, changer une contrainte en NOCHECK revient à changer la structure de la database à la volée – les contraintes de suppression sont certainement – et à éviter (par exemple, les users ont besoin de privilèges accrus).

 SET NOCOUNT ON DECLARE @table TABLE( RowId INT PRIMARY KEY IDENTITY(1, 1), ForeignKeyConstraintName NVARCHAR(200), ForeignKeyConstraintTableSchema NVARCHAR(200), ForeignKeyConstraintTableName NVARCHAR(200), ForeignKeyConstraintColumnName NVARCHAR(200), PrimaryKeyConstraintName NVARCHAR(200), PrimaryKeyConstraintTableSchema NVARCHAR(200), PrimaryKeyConstraintTableName NVARCHAR(200), PrimaryKeyConstraintColumnName NVARCHAR(200), UpdateRule NVARCHAR(100), DeleteRule NVARCHAR(100) ) INSERT INTO @table(ForeignKeyConstraintName, ForeignKeyConstraintTableSchema, ForeignKeyConstraintTableName, ForeignKeyConstraintColumnName) SELECT U.CONSTRAINT_NAME, U.TABLE_SCHEMA, U.TABLE_NAME, U.COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE U INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C ON U.CONSTRAINT_NAME = C.CONSTRAINT_NAME WHERE C.CONSTRAINT_TYPE = 'FOREIGN KEY' UPDATE @table SET T.PrimaryKeyConstraintName = R.UNIQUE_CONSTRAINT_NAME, T.UpdateRule = R.UPDATE_RULE, T.DeleteRule = R.DELETE_RULE FROM @table T INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R ON T.ForeignKeyConstraintName = R.CONSTRAINT_NAME UPDATE @table SET PrimaryKeyConstraintTableSchema = TABLE_SCHEMA, PrimaryKeyConstraintTableName = TABLE_NAME FROM @table T INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C ON T.PrimaryKeyConstraintName = C.CONSTRAINT_NAME UPDATE @table SET PrimaryKeyConstraintColumnName = COLUMN_NAME FROM @table T INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE U ON T.PrimaryKeyConstraintName = U.CONSTRAINT_NAME --SELECT * FROM @table SELECT ' BEGIN TRANSACTION BEGIN TRY' --DROP CONSTRAINT: SELECT ' ALTER TABLE [' + ForeignKeyConstraintTableSchema + '].[' + ForeignKeyConstraintTableName + '] DROP CONSTRAINT ' + ForeignKeyConstraintName + ' ' FROM @table SELECT ' END TRY BEGIN CATCH ROLLBACK TRANSACTION RAISERROR(''Operation failed.'', 16, 1) END CATCH IF(@@TRANCOUNT != 0) BEGIN COMMIT TRANSACTION RAISERROR(''Operation completed successfully.'', 10, 1) END ' --ADD CONSTRAINT: SELECT ' BEGIN TRANSACTION BEGIN TRY' SELECT ' ALTER TABLE [' + ForeignKeyConstraintTableSchema + '].[' + ForeignKeyConstraintTableName + '] ADD CONSTRAINT ' + ForeignKeyConstraintName + ' FOREIGN KEY(' + ForeignKeyConstraintColumnName + ') REFERENCES [' + PrimaryKeyConstraintTableSchema + '].[' + PrimaryKeyConstraintTableName + '](' + PrimaryKeyConstraintColumnName + ') ON UPDATE ' + UpdateRule + ' ON DELETE ' + DeleteRule + ' ' FROM @table SELECT ' END TRY BEGIN CATCH ROLLBACK TRANSACTION RAISERROR(''Operation failed.'', 16, 1) END CATCH IF(@@TRANCOUNT != 0) BEGIN COMMIT TRANSACTION RAISERROR(''Operation completed successfully.'', 10, 1) END' GO 

  --Drop and Recreate Foreign Key Constraints SET NOCOUNT ON DECLARE @table TABLE( RowId INT PRIMARY KEY IDENTITY(1, 1), ForeignKeyConstraintName NVARCHAR(200), ForeignKeyConstraintTableSchema NVARCHAR(200), ForeignKeyConstraintTableName NVARCHAR(200), ForeignKeyConstraintColumnName NVARCHAR(200), PrimaryKeyConstraintName NVARCHAR(200), PrimaryKeyConstraintTableSchema NVARCHAR(200), PrimaryKeyConstraintTableName NVARCHAR(200), PrimaryKeyConstraintColumnName NVARCHAR(200) ) INSERT INTO @table(ForeignKeyConstraintName, ForeignKeyConstraintTableSchema, ForeignKeyConstraintTableName, ForeignKeyConstraintColumnName) SELECT U.CONSTRAINT_NAME, U.TABLE_SCHEMA, U.TABLE_NAME, U.COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE U INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C ON U.CONSTRAINT_NAME = C.CONSTRAINT_NAME WHERE C.CONSTRAINT_TYPE = 'FOREIGN KEY' UPDATE @table SET PrimaryKeyConstraintName = UNIQUE_CONSTRAINT_NAME FROM @table T INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R ON T.ForeignKeyConstraintName = R.CONSTRAINT_NAME UPDATE @table SET PrimaryKeyConstraintTableSchema = TABLE_SCHEMA, PrimaryKeyConstraintTableName = TABLE_NAME FROM @table T INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C ON T.PrimaryKeyConstraintName = C.CONSTRAINT_NAME UPDATE @table SET PrimaryKeyConstraintColumnName = COLUMN_NAME FROM @table T INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE U ON T.PrimaryKeyConstraintName = U.CONSTRAINT_NAME --SELECT * FROM @table --DROP CONSTRAINT: SELECT ' ALTER TABLE [' + ForeignKeyConstraintTableSchema + '].[' + ForeignKeyConstraintTableName + '] DROP CONSTRAINT ' + ForeignKeyConstraintName + ' GO' FROM @table --ADD CONSTRAINT: SELECT ' ALTER TABLE [' + ForeignKeyConstraintTableSchema + '].[' + ForeignKeyConstraintTableName + '] ADD CONSTRAINT ' + ForeignKeyConstraintName + ' FOREIGN KEY(' + ForeignKeyConstraintColumnName + ') REFERENCES [' + PrimaryKeyConstraintTableSchema + '].[' + PrimaryKeyConstraintTableName + '](' + PrimaryKeyConstraintColumnName + ') GO' FROM @table GO 

Je suis d'accord avec vous, Hamlin. Lorsque vous transférez des données à l'aide de SSIS ou lorsque vous souhaitez répliquer des données, il semble tout à fait nécessaire de désactiver ou de supprimer temporairement les contraintes de key étrangère, puis de les réactiver ou de les recréer. Dans ces cas, l'intégrité référentielle n'est pas un problème, car elle est déjà gérée dans la database source. Par conséquent, vous pouvez être assuré à ce sujet.

Premier post 🙂

Pour l'OP, la solution de kristof fonctionnera, à less qu'il y ait des problèmes avec des problèmes massifs de ballons de données et de journaux de transactions lors de grosses suppressions. En outre, même avec le stockage tlog à conserver, puisque les suppressions sont écrites sur le tlog, l'opération peut prendre TRÈS de time pour les tables contenant des centaines de millions de lignes.

J'utilise une série de sliders pour tronquer et recharger fréquemment de grandes copys de l'une de nos énormes bases de données de production. La solution conçue count pour plusieurs schémas, plusieurs colonnes de key étrangère, et le meilleur de tous peut être sproc'd dehors pour l'usage dans SSIS.

Elle implique la création de trois tables de transfert (tables réelles) pour héberger les scripts DROP, CREATE et CHECK FK, la création et l'insertion de ces scripts dans les tables, puis la mise en boucle des tables et leur exécution. Le script joint est constitué de quatre parties: 1.) création et stockage des scripts dans les trois tables de transfert (réelles), 2.) exécution des scripts FK de repository via un slider un par un, 3.) utilisation de sp_MSforeachtable pour tronquer tous les 4. Exécuter les scripts FK et vérifier les scripts FK à la fin de votre package ETL SSIS.

Exécutez la partie de création de script dans une tâche d'exécution SQL dans SSIS. Exécutez la partie "Exécuter Drop FK Scripts" dans une deuxième tâche d'exécution SQL. Placez le script de troncature dans une troisième tâche SQL d'exécution, puis effectuez les autres traitements ETL nécessaires avant d'attacher les scripts CREATE et CHECK dans une tâche finale d'exécution SQL (ou deux si vous le souhaitez) à la fin de votre stream de contrôle.

Le stockage des scripts dans les tables réelles s'est avéré inestimable lorsque la nouvelle application des foreign keys échoue car vous pouvez sélectionner * depuis sync_CreateFK, copyr / coller dans votre window de requête, les exécuter un à la fois, et résoudre les problèmes de données une fois find ceux qui ont échoué / échouent toujours à réappliquer.

Ne relancez pas le script s'il échoue sans vous assurer que vous réappliquez toutes les keys / vérifications étrangères avant de le faire, sinon vous perdrez probablement des créations et vérifierez les scripts fk lorsque vos tables de transfert seront supprimées. recréé avant la création des scripts à exécuter.

 ---------------------------------------------------------------------------- 1) /* Author: Denmach DateCreated: 2014-04-23 Purpose: Generates SQL statements to DROP, ADD, and CHECK existing constraints for a database. Stores scripts in tables on target database for execution. Executes those stored scripts via independent cursors. DateModified: ModifiedBy Comments: This will eliminate deletes and the T-log ballooning associated with it. */ DECLARE @schema_name SYSNAME; DECLARE @table_name SYSNAME; DECLARE @constraint_name SYSNAME; DECLARE @constraint_object_id INT; DECLARE @referenced_object_name SYSNAME; DECLARE @is_disabled BIT; DECLARE @is_not_for_replication BIT; DECLARE @is_not_trusted BIT; DECLARE @delete_referential_action TINYINT; DECLARE @update_referential_action TINYINT; DECLARE @tsql NVARCHAR(4000); DECLARE @tsql2 NVARCHAR(4000); DECLARE @fkCol SYSNAME; DECLARE @pkCol SYSNAME; DECLARE @col1 BIT; DECLARE @action CHAR(6); DECLARE @referenced_schema_name SYSNAME; --------------------------------Generate scripts to drop all foreign keys in a database -------------------------------- IF OBJECT_ID('dbo.sync_dropFK') IS NOT NULL DROP TABLE sync_dropFK CREATE TABLE sync_dropFK ( ID INT IDENTITY (1,1) NOT NULL , Script NVARCHAR(4000) ) DECLARE FKcursor CURSOR FOR SELECT OBJECT_SCHEMA_NAME(parent_object_id) , OBJECT_NAME(parent_object_id) , name FROM sys.foreign_keys WITH (NOLOCK) ORDER BY 1,2; OPEN FKcursor; FETCH NEXT FROM FKcursor INTO @schema_name , @table_name , @constraint_name WHILE @@FETCH_STATUS = 0 BEGIN SET @tsql = 'ALTER TABLE ' + QUOTENAME(@schema_name) + '.' + QUOTENAME(@table_name) + ' DROP CONSTRAINT ' + QUOTENAME(@constraint_name) + ';'; --PRINT @tsql; INSERT sync_dropFK ( Script ) VALUES ( @tsql ) FETCH NEXT FROM FKcursor INTO @schema_name , @table_name , @constraint_name ; END; CLOSE FKcursor; DEALLOCATE FKcursor; ---------------Generate scripts to create all existing foreign keys in a database -------------------------------- ---------------------------------------------------------------------------------------------------------- IF OBJECT_ID('dbo.sync_createFK') IS NOT NULL DROP TABLE sync_createFK CREATE TABLE sync_createFK ( ID INT IDENTITY (1,1) NOT NULL , Script NVARCHAR(4000) ) IF OBJECT_ID('dbo.sync_createCHECK') IS NOT NULL DROP TABLE sync_createCHECK CREATE TABLE sync_createCHECK ( ID INT IDENTITY (1,1) NOT NULL , Script NVARCHAR(4000) ) DECLARE FKcursor CURSOR FOR SELECT OBJECT_SCHEMA_NAME(parent_object_id) , OBJECT_NAME(parent_object_id) , name , OBJECT_NAME(referenced_object_id) , OBJECT_ID , is_disabled , is_not_for_replication , is_not_trusted , delete_referential_action , update_referential_action , OBJECT_SCHEMA_NAME(referenced_object_id) FROM sys.foreign_keys WITH (NOLOCK) ORDER BY 1,2; OPEN FKcursor; FETCH NEXT FROM FKcursor INTO @schema_name , @table_name , @constraint_name , @referenced_object_name , @constraint_object_id , @is_disabled , @is_not_for_replication , @is_not_trusted , @delete_referential_action , @update_referential_action , @referenced_schema_name; WHILE @@FETCH_STATUS = 0 BEGIN BEGIN SET @tsql = 'ALTER TABLE ' + QUOTENAME(@schema_name) + '.' + QUOTENAME(@table_name) + CASE @is_not_trusted WHEN 0 THEN ' WITH CHECK ' ELSE ' WITH NOCHECK ' END + ' ADD CONSTRAINT ' + QUOTENAME(@constraint_name) + ' FOREIGN KEY ('; SET @tsql2 = ''; DECLARE ColumnCursor CURSOR FOR SELECT COL_NAME(fk.parent_object_id , fkc.parent_column_id) , COL_NAME(fk.referenced_object_id , fkc.referenced_column_id) FROM sys.foreign_keys fk WITH (NOLOCK) INNER JOIN sys.foreign_key_columns fkc WITH (NOLOCK) ON fk.[object_id] = fkc.constraint_object_id WHERE fkc.constraint_object_id = @constraint_object_id ORDER BY fkc.constraint_column_id; OPEN ColumnCursor; SET @col1 = 1; FETCH NEXT FROM ColumnCursor INTO @fkCol, @pkCol; WHILE @@FETCH_STATUS = 0 BEGIN IF (@col1 = 1) SET @col1 = 0; ELSE BEGIN SET @tsql = @tsql + ','; SET @tsql2 = @tsql2 + ','; END; SET @tsql = @tsql + QUOTENAME(@fkCol); SET @tsql2 = @tsql2 + QUOTENAME(@pkCol); --PRINT '@tsql = ' + @tsql --PRINT '@tsql2 = ' + @tsql2 FETCH NEXT FROM ColumnCursor INTO @fkCol, @pkCol; --PRINT 'FK Column ' + @fkCol --PRINT 'PK Column ' + @pkCol END; CLOSE ColumnCursor; DEALLOCATE ColumnCursor; SET @tsql = @tsql + ' ) REFERENCES ' + QUOTENAME(@referenced_schema_name) + '.' + QUOTENAME(@referenced_object_name) + ' (' + @tsql2 + ')'; SET @tsql = @tsql + ' ON UPDATE ' + CASE @update_referential_action WHEN 0 THEN 'NO ACTION ' WHEN 1 THEN 'CASCADE ' WHEN 2 THEN 'SET NULL ' ELSE 'SET DEFAULT ' END + ' ON DELETE ' + CASE @delete_referential_action WHEN 0 THEN 'NO ACTION ' WHEN 1 THEN 'CASCADE ' WHEN 2 THEN 'SET NULL ' ELSE 'SET DEFAULT ' END + CASE @is_not_for_replication WHEN 1 THEN ' NOT FOR REPLICATION ' ELSE '' END + ';'; END; -- PRINT @tsql INSERT sync_createFK ( Script ) VALUES ( @tsql ) -------------------Generate CHECK CONSTRAINT scripts for a database ------------------------------ ---------------------------------------------------------------------------------------------------------- BEGIN SET @tsql = 'ALTER TABLE ' + QUOTENAME(@schema_name) + '.' + QUOTENAME(@table_name) + CASE @is_disabled WHEN 0 THEN ' CHECK ' ELSE ' NOCHECK ' END + 'CONSTRAINT ' + QUOTENAME(@constraint_name) + ';'; --PRINT @tsql; INSERT sync_createCHECK ( Script ) VALUES ( @tsql ) END; FETCH NEXT FROM FKcursor INTO @schema_name , @table_name , @constraint_name , @referenced_object_name , @constraint_object_id , @is_disabled , @is_not_for_replication , @is_not_trusted , @delete_referential_action , @update_referential_action , @referenced_schema_name; END; CLOSE FKcursor; DEALLOCATE FKcursor; --SELECT * FROM sync_DropFK --SELECT * FROM sync_CreateFK --SELECT * FROM sync_CreateCHECK --------------------------------------------------------------------------- 2.) ----------------------------------------------------------------------------------------------------------------- ----------------------------execute Drop FK Scripts -------------------------------------------------- DECLARE @scriptD NVARCHAR(4000) DECLARE DropFKCursor CURSOR FOR SELECT Script FROM sync_dropFK WITH (NOLOCK) OPEN DropFKCursor FETCH NEXT FROM DropFKCursor INTO @scriptD WHILE @@FETCH_STATUS = 0 BEGIN --PRINT @scriptD EXEC (@scriptD) FETCH NEXT FROM DropFKCursor INTO @scriptD END CLOSE DropFKCursor DEALLOCATE DropFKCursor -------------------------------------------------------------------------------- 3.) ------------------------------------------------------------------------------------------------------------------ ----------------------------Truncate all tables in the database other than our staging tables -------------------- ------------------------------------------------------------------------------------------------------------------ EXEC sp_MSforeachtable 'IF OBJECT_ID(''?'') NOT IN ( ISNULL(OBJECT_ID(''dbo.sync_createCHECK''),0), ISNULL(OBJECT_ID(''dbo.sync_createFK''),0), ISNULL(OBJECT_ID(''dbo.sync_dropFK''),0) ) BEGIN TRY TRUNCATE TABLE ? END TRY BEGIN CATCH PRINT ''Truncation failed on''+ ? +'' END CATCH;' GO ------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------- ----------------------------execute Create FK Scripts and CHECK CONSTRAINT Scripts--------------- ----------------------------tack me at the end of the ETL in a SQL task------------------------- ------------------------------------------------------------------------------------------------- DECLARE @scriptC NVARCHAR(4000) DECLARE CreateFKCursor CURSOR FOR SELECT Script FROM sync_createFK WITH (NOLOCK) OPEN CreateFKCursor FETCH NEXT FROM CreateFKCursor INTO @scriptC WHILE @@FETCH_STATUS = 0 BEGIN --PRINT @scriptC EXEC (@scriptC) FETCH NEXT FROM CreateFKCursor INTO @scriptC END CLOSE CreateFKCursor DEALLOCATE CreateFKCursor ------------------------------------------------------------------------------------------------- DECLARE @scriptCh NVARCHAR(4000) DECLARE CreateCHECKCursor CURSOR FOR SELECT Script FROM sync_createCHECK WITH (NOLOCK) OPEN CreateCHECKCursor FETCH NEXT FROM CreateCHECKCursor INTO @scriptCh WHILE @@FETCH_STATUS = 0 BEGIN --PRINT @scriptCh EXEC (@scriptCh) FETCH NEXT FROM CreateCHECKCursor INTO @scriptCh END CLOSE CreateCHECKCursor DEALLOCATE CreateCHECKCursor 

Trouver la contrainte

 SELECT * FROM sys.foreign_keys WHERE referenced_object_id = object_id('TABLE_NAME') 

Exécute le SQL généré par ce SQL

 SELECT 'ALTER TABLE ' + OBJECT_SCHEMA_NAME(parent_object_id) + '.[' + OBJECT_NAME(parent_object_id) + '] DROP CONSTRAINT ' + name FROM sys.foreign_keys WHERE referenced_object_id = object_id('TABLE_NAME') 

Safeway.

Remarque: Ajout de la solution pour supprimer la contrainte afin que la table puisse être supprimée ou modifiée sans aucune erreur de contrainte.

WITH CHECK CHECK est presque certainement nécessaire!

Ce point a été soulevé dans certaines réponses et commentaires, mais je pense que c'est assez important pour l'appeler de nouveau.

Ré-activer une contrainte en utilisant la command suivante (non WITH CHECK ) aura de sérieux inconvénients .

 ALTER TABLE MyTable CHECK CONSTRAINT MyConstraint; 

AVEC CHECK | AVEC NOCHECK

Indique si datatables de la table sont ou non validées par rapport à une contrainte FOREIGN KEY ou CHECK nouvellement ajoutée ou ré-activée. Si elle n'est pas spécifiée, WITH CHECK est supposé pour les nouvelles contraintes et WITH NOCHECK est supposé pour les contraintes ré-activées.

Si vous ne souhaitez pas vérifier les nouvelles contraintes CHECK ou FOREIGN KEY par rapport aux données existantes, utilisez WITH NOCHECK. Nous ne recommandons pas de le faire, sauf dans de rares cas. La nouvelle contrainte sera évaluée dans toutes les mises à jour de données ultérieures. Toute violation de contrainte supprimée par WITH NOCHECK lors de l'ajout de la contrainte peut entraîner l'échec des futures mises à jour si elles mettent à jour des lignes avec des données non conforms à la contrainte.

L'optimiseur de requête ne prend pas en count les contraintes définies avec NOCHECK. Ces contraintes sont ignorées jusqu'à ce qu'elles soient réactivées en utilisant la table ALTER TABLE WITH CHECK CHECK CONSTRAINT ALL.

Remarque: WITH NOCHECK est la valeur par défaut pour la réactivation des contraintes. Je dois me requestr pourquoi …

1. Aucune donnée existante dans la table ne sera évaluée pendant l'exécution de cette command – l'achèvement réussi n'est aucune garantie que datatables dans la table sont valides selon la contrainte.
2. Lors de la prochaine mise à jour des loggings non valides, la contrainte sera évaluée et échouera, ce qui entraînera des erreurs sans rapport avec la mise à jour effectuée.
3. La logique d'application qui repose sur la contrainte pour garantir que datatables sont valides peut échouer.
4. L'optimiseur de requête n'utilisera aucune contrainte activée de cette manière.

La vue système sys.foreign_keys offre une certaine visibilité sur le problème. Notez qu'il possède à la fois une is_disabled et une colonne is_not_trusted . is_disabled indique si les opérations de manipulation de données futures seront validées par rapport à la contrainte. is_not_trusted indique si toutes datatables actuellement dans la table ont été validées par rapport à la contrainte.

 ALTER TABLE MyTable WITH CHECK CHECK CONSTRAINT MyConstraint; 

Vos contraintes sont-elles fiables? Trouver…

 SELECT * FROM sys.foreign_keys WHERE is_not_trusted = 1; 

Cliquez avec le button droit sur la design de la table et accédez à Relations et choisissez la key étrangère dans le volet gauche et dans le volet droit, définissez Appliquer la contrainte de key étrangère à 'Oui' (pour activer les contraintes de key étrangère) ou 'Non'. le désactiver).

Vous devriez être en mesure de désactiver les contraintes de key étrangère de la même façon que vous désactivez temporairement d'autres contraintes:

 Alter table MyTable nocheck constraint FK_ForeignKeyConstraintName 

Assurez-vous de désactiver la contrainte sur la première table répertoriée dans le nom de la contrainte. Par exemple, si ma contrainte de key étrangère était FK_LocationsEmployeesLocationIdEmployeeId, je voudrais utiliser ce qui suit:

 Alter table Locations nocheck constraint FK_LocationsEmployeesLocationIdEmployeeId 

même si la violation de cette contrainte produit une erreur qui n'indique pas nécessairement cette table comme source du conflit.

J'ai une version plus utile si vous êtes intéressé. J'ai levé un peu de code d'ici http://www.dbaservices.com.au/news/drop-recreate-foreign-key-constraints/ en le modifiant pour permettre un tableau de tables dans la procédure stockée et il remplit la drop , tronque, ajoute des instructions avant de les exécuter toutes. Cela vous permet de décider quelles tables doivent être tronquées.

 /****** Object: UserDefinedTableType [util].[typ_objects_for_managing] Script Date: 03/04/2016 16:42:55 ******/ CREATE TYPE [util].[typ_objects_for_managing] AS TABLE( [schema] [sysname] NOT NULL, [object] [sysname] NOT NULL ) GO create procedure [util].[truncate_table_with_constraints] @objects_for_managing util.typ_objects_for_managing readonly --@schema sysname --,@table sysname as --select -- @table = 'TABLE', -- @schema = 'SCHEMA' declare @exec_table as table (ordinal int identity (1,1), statement nvarchar(4000), primary key (ordinal)); --print '/*Drop Foreign Key Statements for ['+@schema+'].['+@table+']*/' insert into @exec_table (statement) select 'ALTER TABLE ['+SCHEMA_NAME(o.schema_id)+'].['+ o.name+'] DROP CONSTRAINT ['+fk.name+']' from sys.foreign_keys fk inner join sys.objects o on fk.parent_object_id = o.object_id where exists ( select * from @objects_for_managing chk where chk.[schema] = SCHEMA_NAME(o.schema_id) and chk.[object] = o.name ) ; --o.name = @table and --SCHEMA_NAME(o.schema_id) = @schema insert into @exec_table (statement) select 'TRUNCATE TABLE ' + src.[schema] + '.' + src.[object] from @objects_for_managing src ; --print '/*Create Foreign Key Statements for ['+@schema+'].['+@table+']*/' insert into @exec_table (statement) select 'ALTER TABLE ['+SCHEMA_NAME(o.schema_id)+'].['+o.name+'] ADD CONSTRAINT ['+fk.name+'] FOREIGN KEY (['+c.name+']) REFERENCES ['+SCHEMA_NAME(refob.schema_id)+'].['+refob.name+'](['+refcol.name+'])' from sys.foreign_key_columns fkc inner join sys.foreign_keys fk on fkc.constraint_object_id = fk.object_id inner join sys.objects o on fk.parent_object_id = o.object_id inner join sys.columns c on fkc.parent_column_id = c.column_id and o.object_id = c.object_id inner join sys.objects refob on fkc.referenced_object_id = refob.object_id inner join sys.columns refcol on fkc.referenced_column_id = refcol.column_id and fkc.referenced_object_id = refcol.object_id where exists ( select * from @objects_for_managing chk where chk.[schema] = SCHEMA_NAME(o.schema_id) and chk.[object] = o.name ) ; --o.name = @table and --SCHEMA_NAME(o.schema_id) = @schema declare @looper int , @total_records int, @sql_exec nvarchar(4000) select @looper = 1, @total_records = count(*) from @exec_table; while @looper <= @total_records begin select @sql_exec = (select statement from @exec_table where ordinal =@looper) exec sp_executesql @sql_exec print @sql_exec set @looper = @looper + 1 end 

La réponse marquée '905' semble bonne mais ne fonctionne pas.

Après a travaillé pour moi. Toute contrainte de key primaire, key unique ou par défaut NE PEUT PAS être désactivée. In fact, if 'sp_helpconstraint '' shows 'n/a' in status_enabled – Means it can NOT be enabled/disabled.

— To generate script to DISABLE

 select 'ALTER TABLE ' + object_name(id) + ' NOCHECK CONSTRAINT [' + object_name(constid) + ']' from sys.sysconstraints where status & 0x4813 = 0x813 order by object_name(id) 

— To generate script to ENABLE

 select 'ALTER TABLE ' + object_name(id) + ' CHECK CONSTRAINT [' + object_name(constid) + ']' from sys.sysconstraints where status & 0x4813 = 0x813 order by object_name(id) 

One script to rule them all: this combines truncate and delete commands with sp_MSforeachtable so that you can avoid dropping and recreating constraints – just specify the tables that need to be deleted rather than truncated and for my purposes I have included an extra schema filter for good measure (tested in 2008r2)

 declare @schema nvarchar(max) = 'and Schema_Id=Schema_id(''Value'')' declare @deletiontables nvarchar(max) = '(''TableA'',''TableB'')' declare @truncateclause nvarchar(max) = @schema + ' and o.Name not in ' + + @deletiontables; declare @deleteclause nvarchar(max) = @schema + ' and o.Name in ' + @deletiontables; exec sp_MSforeachtable 'alter table ? nocheck constraint all', @whereand=@schema exec sp_MSforeachtable 'truncate table ?', @whereand=@truncateclause exec sp_MSforeachtable 'delete from ?', @whereand=@deleteclause exec sp_MSforeachtable 'alter table ? with check check constraint all', @whereand=@schema