Désactiver les contraintes de clé étrangère avec des instructions INSERT et UPDATE

  • Article

S’applique à : SQL Server 2016 (13.x) et versions ultérieures Azure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)

Vous pouvez désactiver une contrainte de clé étrangère pendant les transactions INSERT et UPDATE dans SQL Server à l’aide de SQL Server Management Studio ou de Transact-SQL. Utilisez cette option si vous savez que de nouvelles données ne violeront pas la contrainte existante ou si la contrainte s’applique uniquement aux données déjà dans la base de données.

Limitations et restrictions

Une fois ces contraintes désactivées, les insertions ou les mises à jour ultérieures sur la colonne ne sont pas validées par rapport aux conditions de la contrainte.

Autorisations

Requiert une autorisation ALTER sur la table.

Utilisez SQL Server Management Studio.

Pour désactiver une contrainte de clé étrangère avec les instructions INSERT et UPDATE

  1. Dans l' Explorateur d'objets, développez la table avec la contrainte, puis développez le dossier Clés .

  2. Cliquez avec le bouton droit sur la contrainte et sélectionnez Modifier.

  3. Dans la grille sous Concepteur de tables, sélectionnez Appliquer la contrainte de clé étrangère, puis sélectionnez Non dans le menu déroulant.

  4. Sélectionnez Fermer.

  5. Pour réactiver la contrainte quand c’est nécessaire, suivez les mêmes étapes que ci-dessus. Sélectionnez Appliquer la contrainte de clé étrangère, puis sélectionnez Oui dans le menu déroulant.

  6. Pour approuver la contrainte en vérifiant les données existantes dans la relation de la clé étrangère, sélectionnez Vérifier les données existantes à la création ou à la réactivation, puis Oui dans le menu déroulant. Cela garantit que la contrainte de clé étrangère est approuvée.

  • Si Vérifier les données existantes à la création ou à la réactivation est défini sur Non, la clé étrangère ne vérifie pas les données existantes lorsqu’elle est réactivée. L’optimiseur de requête ne peut donc pas envisager d’améliorations potentielles des performances. Les clés étrangères approuvées sont recommandées, car elles peuvent être utilisées pour simplifier les plans d’exécution avec des hypothèses basées sur la contrainte de clé étrangère. Pour vérifier si les clés étrangères sont approuvées dans votre base de données, vous trouverez un exemple de requête plus loin dans cet article.

Utiliser Transact-SQL

Pour désactiver une contrainte de clé étrangère avec les instructions INSERT et UPDATE

  1. Dans l' Explorateur d'objets, connectez-vous à une instance du Moteur de base de données.

  2. Dans la barre d’outils standard, sélectionnez Nouvelle requête.

  3. Copiez et collez l’exemple suivant dans la fenêtre de requête, puis sélectionnez Exécuter.

    USE AdventureWorks2022;  
GO  
ALTER TABLE Purchasing.PurchaseOrderHeader  
NOCHECK CONSTRAINT FK_PurchaseOrderHeader_Employee_EmployeeID;  
GO

  4. Pour réactiver la contrainte quand vous le souhaitez, copiez et collez l’exemple suivant dans la fenêtre de requête, puis sélectionnez Exécuter.

    USE AdventureWorks2022;  
GO  
ALTER TABLE Purchasing.PurchaseOrderHeader  
CHECK CONSTRAINT FK_PurchaseOrderHeader_Employee_EmployeeID;  
GO

  5. Vérifiez que la contrainte dans votre environnement est approuvée et activée. Si is_not_trusted = 1, la clé étrangère ne vérifie pas les données existantes lorsqu’elle est réactivée ou recréée. L’optimiseur de requête ne peut donc pas envisager d’améliorations potentielles des performances. Les clés étrangères approuvées sont recommandées, car elles peuvent être utilisées pour simplifier les plans d’exécution avec des hypothèses basées sur la contrainte de clé étrangère. Copiez et collez l’exemple suivant dans la fenêtre de requête, puis sélectionnez Exécuter.

    SELECT o.name, fk.name, fk.is_not_trusted, fk.is_disabled
FROM sys.foreign_keys AS fk
INNER JOIN sys.objects AS o ON fk.parent_object_id = o.object_id
WHERE fk.name = 'FK_PurchaseOrderHeader_Employee_EmployeeID';
GO

    Vous devez définir la contrainte de clé étrangère comme approuvée si les données existantes de la table sont conformes à la contrainte de clé étrangère. Pour définir la clé étrangère comme approuvée, utilisez le script suivant pour approuver à nouveau la contrainte de clé étrangère, en notant la syntaxe WITH CHECK supplémentaire. Copiez et collez l’exemple suivant dans la fenêtre de requête, puis sélectionnez Exécuter.

    ALTER TABLE [Purchasing].[PurchaseOrderHeader] 
WITH CHECK 
CHECK CONSTRAINT FK_PurchaseOrderHeader_Employee_EmployeeID;
GO

Étapes suivantes