Désactiver temporairement toutes les contraintes de clé étrangère
j'exécute un paquet SSIS qui remplacera les données pour quelques tables de FlatFiles à des tables existantes dans une base de données.
mon paquet va tronquer les tables et ensuite insérer les nouvelles données. Quand j'exécute mon paquet SSIS, j'obtiens une exception à cause des clés étrangères.
puis-je désactiver les contraintes, lancer mon import, puis les réactiver?
7 réponses
Pour désactiver les contraintes de clé étrangère:
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;
Pour l'activer:
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;
Toutefois, vous ne serez pas en mesure de troncature les tables, vous devrez les supprimer dans le bon ordre. Si vous avez besoin d' troncature elles, vous devez laisser tomber les contraintes entièrement, et les recréer. C'est simple à faire si vos contraintes de clé étrangère sont toutes simples, des contraintes à une seule colonne, mais certainement plus complexes s'il y a plusieurs les colonnes concernées.
voici quelque chose que vous pouvez essayer. Pour que cela fasse partie de votre paquet SSIS, vous aurez besoin d'un endroit pour stocker les définitions FK pendant que le paquet SSIS court (vous ne pourrez pas faire tout cela en un seul script). Ainsi, dans une base de données d'Utilités, créez une table:
CREATE TABLE dbo.PostCommand(cmd NVARCHAR(MAX));
Puis dans votre base de données, vous pouvez avoir une procédure stockée qui fait ceci:
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
maintenant que votre paquet SSIS est terminé, il devrait appeler une procédure stockée différente, qui ne:
DECLARE @sql NVARCHAR(MAX);
SELECT @sql = cmd FROM other_database.dbo.PostCommand;
EXEC sp_executesql @sql;
si vous faites tout cela juste pour pouvoir tronquer au lieu de supprimer, je suggère juste de prendre le hit et d'exécuter une suppression. Peut-être utiliser le modèle de récupération en vrac pour minimiser l'impact du journal. En général, je ne vois pas comment cette solution sera beaucoup plus rapide que de simplement utiliser une suppression dans le bon ordre.
En 2014, j'ai publié une version plus élaborée de post à ce sujet ici:
Utiliser sp_msforeachtable procédure stockée.
Pour désactiver toutes les contraintes:
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT ALL";
pour activer toutes les contraintes:
EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL";
supprimer tous les tableaux:
EXEC sp_msforeachtable "DROP TABLE ?";
Une bonne référence est donnée à : http://msdn.microsoft.com/en-us/magazine/cc163442.aspx sous la rubrique "désactiver toutes les clés étrangères"
inspiré de cela, une approche peut être faite en créant une table temporaire et en insérant les contraintes dans cette table, puis en abandonnant les contraintes et en les réappliquant de cette table temporaire. C'est assez dit ici ce dont je parle
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
Désactiver toutes les contraintes de table
ALTER TABLE TableName NOCHECK CONSTRAINT ConstraintName
-- Permettre à toutes les contraintes de table
ALTER TABLE TableName CHECK CONSTRAINT ConstraintName
pas besoin d'exécuter des requêtes vers des FKs sidables sur sql. Si vous avez un FK à partir de la table de A à B, vous devez:
- supprimer les données d'un tableau
- supprimer les données du tableau B
- insérez les données sur B
- insérez des données sur un
vous pouvez aussi dire à la destination de ne pas vérifier les contraintes
tronquer la table ne sera pas possible même si vous désactivez les clés étrangères.donc vous pouvez utiliser supprimer la commande pour supprimer tous les enregistrements de la table,mais être conscient si vous utilisez supprimer commande pour une table qui se compose de millions d'enregistrements alors votre paquet sera lent et votre taille de journal de transaction va augmenter et il peut remplir votre espace disque précieux.
si vous supprimez les contraintes il peut arriver que vous remplissiez votre table avec des données impures et lorsque vous essayez pour recréer les contraintes il se peut qu'il ne vous le permette pas car il donnera des erreurs. assurez-vous donc que si vous supprimez les contraintes,vous chargez des données qui sont correctement reliées les unes aux autres et satisfiez les relations de contraintes que vous allez recréer.
merci donc de rééchir soigneusement les avantages et les inconvénients de chaque méthode et de l'utiliser selon vos besoins
désactiver tous les index (y compris le pk, qui va désactiver tous les FK), puis réenrêter les PK.
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 votre chargement de données]
Puis tout ramener à la vie...
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