Comment les contraintes de clé étrangère peuvent-elles être temporairement désactivées en utilisant T-SQL?
est-ce que les contraintes de clé étrangère sont supportées dans SQL Server? Ou est-ce que ma seule option pour drop et puis re - create les contraintes?
16 réponses
si vous voulez désactiver toutes les contraintes de la base de données, lancez simplement ce code:
-- disable all constraints
EXEC sp_MSforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
pour les rallumer, Lancez: (l'impression est bien sûr optionnelle et ne fait que lister les tables)
-- enable all constraints
exec sp_MSforeachtable @command1="print '?'", @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"
je le trouve utile lors de la saisie de données d'une base de données à une autre. Elle est bien meilleure approche que de laisser tomber les contraintes. Comme vous l'avez mentionné, il est pratique de laisser tomber toutes les données de la base de données et de les repeupler (dire environnement de test).
si vous supprimez toutes les données, vous pouvez trouver cette solution utile.
aussi parfois il est pratique de désactiver tous les déclencheurs aussi bien, 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 est de laisser tomber et de créer des contraintes de clé étrangère.
Je n'ai pas trouvé d'exemples dans ce post qui fonctionnerait pour moi" tel quel", l'un ne fonctionnerait pas si les clés étrangères renvoient à des schémas différents, l'autre ne fonctionnerait pas si les clés étrangères renvoient à plusieurs colonnes. Ce script prend en compte les deux, plusieurs schémas et plusieurs colonnes par clé étrangère.
voici le script qui génère des instructions" ADD CONSTRAINT", pour plusieurs colonnes il les séparera par virgule ( assurez-vous de sauvegarder cette sortie avant d'exécuter les DROP statements ):
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 déclarations "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 modifier la table en utilisant NOCHECK
ALTER TABLE [TABLE_NAME] NOCHECK CONSTRAINT [ALL|CONSTRAINT_NAME]
pour vous permettre d'utiliser le double cochez
ALTER TABLE [TABLE_NAME] WITH CHECK CHECK CONSTRAINT [ALL|CONSTRAINT_NAME]
- attention au double CHECK CHECK CHECK lors de l'activation.
- tous les moyens pour toutes les contraintes dans le tableau.
une fois rempli, si vous devez vérifier le statut, utiliser ce script pour lister 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
la norme SQL-92 permet de déclarer une constatation comme étant AJOURNABLE de sorte qu'elle puisse être reportée (implicitement ou explicitement) dans le cadre d'une transaction. Malheureusement, SQL Server manque toujours cette fonctionnalité SQL-92.
pour moi, changer une contrainte en NOCHECK s'apparente à changer la structure de la base de données à la volée -- supprimer les contraintes est certainement -- et quelque chose à éviter (par exemple les utilisateurs ont besoin de privilèges accrus).
--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 toi, Hamlin. Lorsque vous transférez des données en utilisant SSIS ou lorsque vous voulez répliquer des données, il semble tout à fait nécessaire de désactiver ou de supprimer temporairement les contraintes de clé étrangère et 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à maintenue dans la base de données source. Par conséquent, vous pouvez être assuré au sujet de cette affaire.
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
Premier post :)
pour L'OP, la solution de kristof fonctionnera, à moins qu'il n'y ait des problèmes avec les données massives et les problèmes de ballonnets de journal de transactions sur les grandes suppressions. En outre, même avec le stockage tlog pour épargner, puisque les suppressions écrivent à la tlog, l'opération peut prendre un temps très long pour les tables avec des centaines de millions de lignes.
j'utilise une série de curseurs pour tronquer et recharger de grandes copies d'une de nos énormes bases de données de production fréquemment. Le la solution a été conçue pour plusieurs schémas, plusieurs colonnes de clés étrangères, et le meilleur de tous peut être sproc'D pour une utilisation dans les petites applications.
cela implique la création de trois tables de mise en scène (tables réelles) pour héberger la chute, créer, et vérifier les scripts FK, la création et l'insertion de ces scripts dans les tables, puis en boucle sur les tables et les exécuter. Le texte ci-joint comporte quatre parties: 1. la création et le stockage des scripts dans les trois mise en scène (réel) des tables, 2.) exécution des scripts drop FK via un curseur un par un, 3.) En utilisant sp_MSforeachtable pour tronquer toutes les tables de la base de données autres que nos trois tables de staging et 4.) exécution des scripts create FK et check FK à la fin de votre paquet ETL SSIS.
exécutez la partie de création de script dans une tâche SQL D'exécution dans SSIS. Exécutez la partie" execute Drop FK Scripts " dans une seconde exécutez la tâche SQL. Mettre le script de troncature dans une troisième exécuter la tâche SQL, puis effectuer quels que soient les autres processus ETL que vous devez faire avant de joindre les scripts CREATE Et CHECK dans une tâche SQL finale Execute (ou deux si vous le souhaitez) à la fin de votre flux de contrôle.
le stockage des scripts dans les tables réelles s'est avéré inestimable lorsque la ré-application des clés étrangères échoue car vous pouvez sélectionner * à partir de sync_CreateFK, copier/coller dans votre fenêtre de requête, les exécuter un à la fois, et corriger les problèmes de données une fois que vous trouvez ceux qui ont échoué/sont encore en train de ne pas s'appliquer.
ne relancez pas le script s'il échoue sans vous assurer que vous ré-appliquez toutes les clés/vérifications étrangères avant de le faire, ou vous perdrez très probablement une certaine création et vérifiez les scripts fk car nos tables de mise en scène sont abandonnées et recréées 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
WITH CHECK CHECK est presque certainement nécessaire!
ce point a été soulevé dans certaines réponses et observations, mais je pense qu'il est suffisamment important de le rappeler.
réactiver une contrainte à l'aide de la commande suivante (no WITH CHECK ) aura quelques inconvénients graves .
ALTER TABLE MyTable CHECK CONSTRAINT MyConstraint;
AVEC CONTRÔLE | SANS CONTRÔLE
spécifie si les données du tableau sont validées ou non par rapport à une clé étrangère ou une contrainte de contrôle nouvellement ajoutée ou réactivée. Si ce n' spécifié, avec contrôle est supposé pour les nouvelles contraintes, et avec NOCHECK est supposé pour les contraintes réactivées.
si vous ne voulez pas vérifier une nouvelle vérification ou des contraintes de clé étrangère contre les données existantes, utiliser avec NOCHECK. Nous ne recommandons pas de le faire de cette, sauf dans de rares cas. La nouvelle contrainte sera évalué dans toutes les mises à jour ultérieures. Toute violation de contrainte supprimée avec NOCHECK lorsque la contrainte est ajoutée peut causer des mises à jour futures pour échouer s'ils mettent à jour des lignes avec des données qui ne sont pas conformes contrainte.
L'optimiseur de requête ne prend pas en compte les contraintes définies AVEC NOCHECK. Ces contraintes sont ignorées jusqu'à ce qu'elles soient réactivées en utilisant ALTER TABLE table avec Check Check contrainte ALL.
Note: avec NOCHECK est la valeur par défaut pour les contraintes de réactivation. Je me demande pourquoi...
- Aucune donnée existante dans le tableau ne sera évaluée lors de l'exécution de cette commande - la réussite de cette commande ne garantit pas que les données dans le tableau sont valides selon la contrainte.
- lors de la prochaine mise à jour des enregistrements Invalides, la contrainte sera évaluée et échouera - résultant en des erreurs qui peuvent être sans rapport avec la mise à jour réelle qui est faite.
- la logique D'Application qui s'appuie sur la contrainte pour s'assurer que les données sont valides peut échouer.
- l'optimiseur de requête n'utilisera aucune contrainte activée de cette manière.
Le sys.foreign_keys vue système fournit une certaine visibilité sur la question. Notez qu'il a à la fois un is_disabled et un is_not_trusted de la colonne. is_disabled indique si les opérations futures de manipulation de données seront validées par rapport à la contrainte. is_not_trusted indique si toutes les données actuellement dans le tableau ont été validées par rapport à la contrainte.
ALTER TABLE MyTable WITH CHECK CHECK CONSTRAINT MyConstraint;
vos contraintes sont-elles fiables? Savoir...
SELECT * FROM sys.foreign_keys WHERE is_not_trusted = 1;
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.
Note: ajout d'une solution pour supprimer la contrainte afin que la table puisse être supprimée ou modifiée sans aucune erreur de contrainte.
faites un clic droit sur le design de la table et allez dans relations et choisissez la clé étrangère sur le volet de gauche et dans le volet de droite, positionnez Enforce foreign key contrainte à " Oui "(pour activer les contraintes de clé étrangère) ou " non " (pour la désactiver).
vous devriez en fait pouvoir désactiver les contraintes de clé étrangère de la même manière que vous désactivez temporairement les autres contraintes:
Alter table MyTable nocheck constraint FK_ForeignKeyConstraintName
assurez-vous de désactiver la contrainte sur la première table listée dans le nom de la contrainte. Par exemple, si ma contrainte de clé étrangère était Fk_locationsemployeeslocationid EmployeeID, je voudrais utiliser ce qui suit:
Alter table Locations nocheck constraint FK_LocationsEmployeesLocationIdEmployeeId
même si la violation de cette contrainte produira une erreur cela ne dit pas nécessairement que cette table est la source du conflit.
j'ai une version plus utile si vous êtes intéressé. J'ai relevé un peu de code d'ici http://www.dbaservices.com.au/news/drop-recreate-foreign-key-constraints / le modifiant pour permettre un tableau de tables dans la procédure stockée et il popule la goutte, tronquer, ajouter des déclarations avant de les exécuter toutes. Cela vous donne le contrôle 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.
a travaillé pour moi. N'importe quelle clé primaire, clé Unique ou contrainte par défaut ne peut pas être désactivée. En fait, si 'sp_helpconstraint' montre 'n/a' dans status_enabled - signifie qu'il peut et non être activé/désactivé.
-- Pour générer un script pour DÉSACTIVER
select 'ALTER TABLE ' + object_name(id) + ' NOCHECK CONSTRAINT [' + object_name(constid) + ']'
from sys.sysconstraints
where status & 0x4813 = 0x813 order by object_name(id)
-- pour générer script pour activer
select 'ALTER TABLE ' + object_name(id) + ' CHECK CONSTRAINT [' + object_name(constid) + ']'
from sys.sysconstraints
where status & 0x4813 = 0x813 order by object_name(id)
un script pour les diriger tous: il combine les commandes truncate et delete avec sp_MSforeachtable de sorte que vous pouvez éviter de laisser tomber et de recréer des contraintes - il suffit de spécifier les tables qui doivent être supprimées plutôt que tronquées et pour mes fins j'ai inclus un filtre de schéma supplémentaire pour la bonne mesure (testé en 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
vous pouvez désactiver temporairement les contraintes sur vos tables, faire du travail, puis les reconstruire.
Voici un moyen facile de le faire...
désactiver tous les index, y compris les clés primaires, qui va désactiver toutes les clés étrangères, puis réactiver seulement les clés primaires afin que vous puissiez travailler avec eux...
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
[faire quelque chose, comme charger des données]
puis réactiver et reconstruire les index...
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