Clés étrangères SQL Server: indexation et validation fiable

Un chargement se termine après la désactivation temporaire des clés étrangères. L'application reprend et les nouveaux inserts semblent contrôlés. Des mois plus tard, supprimer un client devient lent et un rapport trouve des commandes sans client. Deux sujets ont été confondus: la validation des données existantes et le coût de recherche des lignes liées.

Une clé étrangère ne crée pas automatiquement un index sur les colonnes de la table enfant. Le parent possède une clé primaire ou unique utilisable comme référence, mais SQL Server doit encore chercher les enfants lors d'une modification ou suppression du parent. Une petite opération peut ainsi parcourir une très grande table.

Distinguer activation et confiance

Exécutez cette requête dans la base concernée. Les autorisations déterminent la visibilité des métadonnées: une liste vide obtenue avec un compte restreint ne démontre pas l'absence de contraintes problématiques.

SELECT
    OBJECT_SCHEMA_NAME(fk.parent_object_id) AS ChildSchema,
    OBJECT_NAME(fk.parent_object_id) AS ChildTable,
    fk.name,
    fk.is_disabled,
    fk.is_not_trusted,
    fk.delete_referential_action_desc
FROM sys.foreign_keys AS fk
WHERE fk.is_disabled = 1 OR fk.is_not_trusted = 1;

Une contrainte activée peut rester non approuvée. La réactiver sans vérifier les anciennes lignes peut contrôler les futures modifications tout en laissant des violations historiques. L'optimiseur ne peut pas faire les mêmes hypothèses qu'avec une relation validée. Un nouvel insert réussi ne prouve donc pas que l'intégralité de la relation est fiable.

Une contrainte désactivée représente un autre état: les écritures futures ne bénéficient plus de ce contrôle. Identifiez la raison de la désactivation et les données modifiées depuis. Si l'application a continué à fonctionner, contrôler uniquement le lot initial ne suffit pas.

Corriger avant de certifier la relation

Les instructions suivantes sont un modèle à adapter à des tables d'exercice existantes dbo.Orders et dbo.Customers. Vérifiez les véritables noms et colonnes. L'anti-jointure recherche les clés client non nulles sans parent; elle ne décide pas s'il faut supprimer les commandes, corriger leur clé ou recréer un client manquant.

SELECT o.CustomerId, COUNT_BIG(*) AS OrphanRows
FROM dbo.Orders AS o
LEFT JOIN dbo.Customers AS c ON c.CustomerId=o.CustomerId
WHERE o.CustomerId IS NOT NULL AND c.CustomerId IS NULL
GROUP BY o.CustomerId;

CREATE INDEX IX_Orders_CustomerId
ON dbo.Orders(CustomerId);

ALTER TABLE dbo.Orders
WITH CHECK CHECK CONSTRAINT FK_Orders_Customers;

Les deux CHECK ont des rôles distincts. WITH CHECK vérifie les données existantes; CHECK CONSTRAINT active la contrainte. Cette validation peut lire beaucoup de données et prendre des verrous. Évaluez le travail, prévoyez une fenêtre adaptée, puis vérifiez is_disabled et is_not_trusted. Le résultat du catalogue vaut mieux qu'une simple confirmation opérateur.

Une clé enfant nullable peut légitimement contenir NULL pour signifier l'absence de relation. Il ne faut pas traiter ces lignes comme des orphelines. Une clé composée exige une comparaison de toutes les colonnes concernées et une réflexion sur leur nullabilité. Une correction par nom affiché, au lieu de la clé déclarée, risque d'inventer de mauvaises associations.

Évaluer les accès dans les deux sens

Inspectez les index existants avant d'ajouter celui de l'exemple. Un index composé commençant par CustomerId peut déjà répondre au besoin. Un index qui place CustomerId après une autre colonne indépendante n'offre généralement pas le même accès. Examinez les plans des suppressions parent et des principales recherches enfant.

Indexer toutes les clés étrangères sans analyse ajoute espace et travail d'écriture. À l'inverse, justifier l'absence d'index par la rareté des jointures peut ignorer les contrôles de suppression et les cascades. Mesurez les deux directions. Une cascade volumineuse produit encore journalisation et blocages même avec un bon index, car les modifications doivent réellement être effectuées.

Pour terminer, testez un enfant valide, un enfant invalide dans une transaction contrôlée et une modification parent représentative dans un environnement sûr. Vérifiez que l'identité applicative reçoit et traite l'erreur attendue. Confiance de la contrainte, intégrité métier et performance sont liées, mais aucune ne remplace les preuves nécessaires aux autres.

Références techniques: Microsoft Learn: Foreign keys · Microsoft Learn: Constraint trust.