> Data > Intégrité référentielle avec SQL Server

Intégrité référentielle avec SQL Server

Data - Par iTPro.fr - Publié le 24 juin 2010
email

par Kalen Delaney
La maintenance des relations logiques entre les tables est un élément essentiel de la gestion de base de données. Voici comment utiliser les nouvelles méthodes d'application de l'intégrité référentielle dans SQL Server 2000.Le maintien de relations solides est primordial.
La création et le maintien de relations logiques entre les tables constituent une partie fondamentale du travail avec des bases de données relationnelles. La plupart des bases de données doivent entretenir certaines relations, sous peine de corruption logique des données. Lorsque de telles relations existent, on dit que ces données disposent d'une intégrité référentielle. L'une des tables est la table référencée et l'autre, la table de référence ; les valeurs de la table de référence doivent correspondre aux valeurs de la table référencée. (Certaines utilisateurs qualifient cette relation de tables parent/enfant. Toutefois, cette terminologie implique une hiérarchie évitée par le modèle relationnel). SQL Server peut mettre en oeuvre automatiquement l'intégrité référentielle à  travers des contraintes de clés étrangères que vous aurez préalablement définies. Cette fonction est appelée intégrité référentielle déclarative (en anglais "Declarative Referential Integrity" ou DRI) en raison de sa présence dans la définition de la table. On peut également utiliser d'autres fonctions, comme les déclencheurs, pour imposer des relations ; on parle alors d'intégrité référentielle de procédure. Dans cet article, je présente comment gérer l'intégrité référentielle dans SQL Server, en accordant une attention particulière aux nouvelles fonctions intéressantes de SQL Server 2000.
SQL Server 7.0 et les versions précédentes disposaient d'une seule méthode pour traiter les tentatives de violation des contraintes de clés étrangères. Si un utilisateur tente de modifier les données d'une table d'une manière qui pourrait violer l'intégrité référentielle (telle qu'elle est définie dans les clés étrangères), SQL Server empêche cette modification et renvoie un message d'erreur. SQL Server 2000 dispose d'une nouvelle fonction cascade pouvant traiter les violations de l'intégrité référentielle d'une autre manière, comme je vais vous le démontrer.
Pour commencer, analysons rapidement un exemple permettant de clarifier ce que représente l'intégrité référentielle. La base de données Northwind dispose d'une table appelée Orders, et d'une autre, appelée Order Details. Dans la table Orders, la colonne OrderId représente la clé primaire identifiant chaque commande de manière unique. La table Order Details possède également une colonne OrderId mais, dans cette table, la colonne est une clé étrangère qui doit correspondre à  un OrderId existant de la table Orders. Dans cet exemple, la table Orders est la table référencée et la table Order Details est la table de référence. Si on définit une contrainte de clé étrangère pour mettre en oeuvre la relation entre les tables Orders et les Order Details, SQL Server vérifie que la modification de l'une de ces tables ne viole pas la relation. Si par exemple on essaye de supprimer un enregistrement de la table Orders alors que l'OrderId de cet enregistrement existe dans la table Order Details, la suppression violera la contrainte d'intégrité référentielle. Tenter de mettre à  jour une colonne OrderId de la table Orders lorsque la valeur d'origine, et non la nouvelle valeur, existe dans les Order Details, constitue également une violation. En outre, SQL Server doit vérifier chaque insertion dans Order Details pour s'assurer que le nouvel OrderId existe dans la table Orders, et doit vérifier toutes les mises à  jour de la colonne OrderId dans Order Details.

Le standard ANSI SQL-92 introduit le concept d’action référentielle. Parfois, au lieu d’empêcher purement et simplement une opération de modification de données qui violerait une référence de clé étrangère, on voudra peut-être que le système de gestion de base de données effectue une autre action compensatoire, autorisant la modification tout en respectant la contrainte. Par exemple, si on supprime un enregistrement de la table Orders référencée par Order Details, on peut indiquer à  SQL Server de supprimer automatiquement tous les enregistrements de la table Order Details (c’est-à -dire supprimer en cascade dans Order Details). De cette manière, on peut modifier la table Orders sans violer la contrainte.
Le standard ANSI définit quatre actions référentielles possibles s’appliquant aux suppressions ou aux mises à  jour de la table référencée : NO ACTION, CASCADE, SET DEFAULT et SET NULL. NO ACTION, l’option par défaut du standard ANSI, empêche la modification. CASCADE autorise une suppression ou une mise à  jour de tous les enregistrements correspondants de la table de référence. SET DEFAULT permet la suppression ou la mise à  jour, mais définit toutes les valeurs de clés étrangères de la table de référence à  leur valeur par défaut. Enfin, SET NULL permet la suppression ou la mise à  jour et met toutes les valeurs de clé étrangère de la table de référence à  NULL.
Vous noterez que ces actions ne s’appliquent qu’aux modifications apportées à  la table référencée. Lorsqu’on modifie la table de référence, il n’existe qu’une action d’intégrité référentielle possible : si on insère ou met à  jour une valeur de la colonne de clé étrangère, la nouvelle valeur doit correspondre à  une valeur de la table référencée. Dans le cas contraire, SQL Server refuse la modification.

Téléchargez gratuitement cette ressource

Endpoint Security : Guide de Mise en œuvre

Endpoint Security : Guide de Mise en œuvre

Détournement d’applications légitimes, élévation de privilèges, logiciels malveillants furtifs : comment les solutions de Endpoint Security permettent elles de faire face aux nouvelles techniques d'attaques complexes ? Découvrez, dans ce Guide Endpoint Security, les perspectives associées à leur mise en œuvre.

Data - Par iTPro.fr - Publié le 24 juin 2010