Worldlex Wiki Une clé étrangère est un ensemble d' attributs d'une table qui fait référence à la clé primaire d'une autre table, établissant ainsi un lien entre ces deux tables. Dans le contexte des bases de données relationnelles , une clé étrangère est soumise à une contrainte d'inclusion : les tuples constitués des attributs de la clé étrangère dans une relation R doivent également exister dans une autre relation S (pas nécessairement distincte) ; de plus, ces attributs doivent également être une clé candidate dans S.
Autrement dit, une clé étrangère est un ensemble d'attributs qui NULL ). Cette règle est appelée contrainte d'intégrité référentielle entre les deux tables . Les violations de ces contraintes pouvant être à l'origine de nombreux problèmes de bases de données, la plupart des systèmes de gestion de bases de données (SGBD) proposent des mécanismes garantissant que chaque clé étrangère non nulle corresponde à une ligne de la table référencée
Prenons l'exemple d'une base de données comportant deux tables : une table CLIENTS contenant toutes les données clients et une table COMMANDES contenant toutes les commandes clients. Supposons que l'entreprise exige que chaque commande soit associée à un client unique. Pour refléter cette exigence dans la base de données, une colonne de clé étrangère est ajoutée à la table COMMANDES (par exemple, ID_CLIENT), qui fait référence à la clé primaire de la table CLIENTS (par exemple, ID). La clé primaire d'une table étant nécessairement unique, et ID_CLIENT ne contenant que les valeurs de ce champ de clé primaire, on peut supposer que, lorsqu'elle est renseignée, ID_CLIENT identifie le client ayant passé la commande. Toutefois, cette hypothèse n'est plus valable si la table COMMANDES n'est pas mise à jour lorsque des lignes de la table CLIENTS sont supprimées ou que la colonne ID est modifiée, ce qui peut compliquer la manipulation de ces tables. De nombreuses bases de données réelles contournent ce problème en « désactivant » les clés étrangères de la table principale plutôt qu'en les supprimant physiquement, ou en utilisant des programmes de mise à jour complexes qui modifient toutes les références à une clé étrangère lorsqu'une modification est nécessaire.
Les clés étrangères jouent un rôle essentiel dans la conception des bases de données . Un aspect important de cette conception consiste à s'assurer que les relations entre les entités du monde réel sont reflétées dans la base de données par des références, grâce à l'utilisation de clés étrangères permettant de faire référence à une table à l'autre. Un autre aspect important est la normalisation des bases de données , qui consiste à segmenter les tables et à les reconstruire à l'aide des clés étrangères.
Plusieurs lignes de la table référençante (ou table enfant) peuvent faire référence à la même ligne de la table référencée (ou table parente). Dans ce cas, la relation entre les deux tables est dite de type « un à plusieurs » .
De plus, la table enfant et la table parent peuvent être en réalité la même table, c'est-à-dire que la clé étrangère fait référence à la même table. Dans SQL Server 2003, on parle alors de clé étrangère autoréférentielle ou récursive. Dans les systèmes de gestion de bases de données, cela se fait souvent en liant une première et une seconde référence à la même table.
Une table peut comporter plusieurs clés étrangères, et chaque clé étrangère peut avoir une table parente différente. Chaque clé étrangère est gérée indépendamment par le système de base de données . Par conséquent, des relations en cascade entre les tables peuvent être établies grâce aux clés étrangères.
Une clé étrangère est définie comme un attribut ou un ensemble d'attributs d'une relation dont les valeurs correspondent à une clé primaire d'une autre relation. La syntaxe permettant d'ajouter une telle contrainte à une table existante est définie dans SQL Server 2003, comme indiqué ci-dessous. Omettre la liste des colonnes dans la REFERENCESclause signifie que la clé étrangère fait référence à la clé primaire de la table cible. De même, les clés étrangères peuvent être définies directement dans l' CREATE TABLEinstruction SQL.
CASCADE
Lorsqu'une ligne de la table parente (référencée) est supprimée (ou mise à jour), les lignes correspondantes de la table enfant (référençant) possédant une colonne de clé étrangère identique sont également supprimées (ou mises à jour). On parle alors de suppression (ou de mise à jour) en cascade.
LIMITER
Une valeur ne peut être mise à jour ou supprimée lorsqu'une ligne existe dans une table référençante ou une table enfant qui référence la valeur dans la table référencée.
De même, une ligne ne peut pas être supprimée tant qu'il existe une référence à celle-ci dans une table référençante ou une table enfant.
Pour mieux comprendre RESTRICT (et CASCADE), il peut être utile de noter la différence suivante, qui n'est pas forcément évidente au premier abord. L'action référentielle CASCADE modifie le comportement de la table (enfant) où le mot CASCADE est utilisé. Par exemple, ON DELETE CASCADE signifie : « Lorsque la ligne référencée est supprimée de l'autre table (table maître), supprimez-la également de moi . » En revanche, l'action référentielle RESTRICT modifie le comportement de la table maître, et non de la table enfant, même si le mot RESTRICT apparaît dans la table enfant et non dans la table maître ! Ainsi, ON DELETE RESTRICT signifie : « Lorsqu'une tentative de suppression de la ligne dans l'autre table (table maître), empêchez la suppression dans cette table (et bien sûr, ne supprimez pas non plus la ligne de moi, mais ce n'est pas le point essentiel ici). »
RESTRICT n'est pas pris en charge par Microsoft SQL 2012 et les versions antérieures.
AUCUNE ACTION
Les actions NO ACTION et RESTRICT sont très similaires. La principale différence réside dans le fait que NO ACTION effectue la vérification d'intégrité référentielle après toute tentative de modification de la table, tandis que RESTRICT la réalise avant l'exécution de l' instruction UPDATE ou DELETE . En cas d'échec de la vérification d'intégrité référentielle, le comportement est identique pour les deux actions : l'instruction UPDATE ou DELETE générera une erreur.
Autrement dit, lorsqu'une instruction UPDATE ou DELETE est exécutée sur la table référencée avec l'action référentielle NO ACTION, le SGBD vérifie à la fin de l'exécution qu'aucune relation référentielle n'est violée. Ceci diffère de RESTRICT, qui suppose d'emblée que l'opération violera la contrainte. Avec NO ACTION, les déclencheurs ou la sémantique de l'instruction elle-même peuvent aboutir à un état final où aucune relation de clé étrangère n'est violée au moment de la vérification finale de la contrainte, permettant ainsi à l'instruction de s'exécuter avec succès.
DÉFINIR NULL, DÉFINIR PAR DÉFAUT
En général, l'action effectuée par le SGBD pour SET NULL ou SET DEFAULT est la même pour ON DELETE ou ON UPDATE : la valeur des attributs référençants concernés est modifiée à NULL pour SET NULL et à la valeur par défaut spécifiée pour SET DEFAULT.
Déclencheurs
Les actions référentielles sont généralement implémentées sous forme de déclencheurs implicites (c'est-à-dire des déclencheurs avec des noms générés par le système, souvent cachés). À ce titre, elles sont soumises aux mêmes limitations que les déclencheurs définis par l'utilisateur, et leur ordre d'exécution par rapport aux autres déclencheurs peut devoir être pris en compte ; dans certains cas, il peut devenir nécessaire de remplacer l'action référentielle par son déclencheur équivalent défini par l'utilisateur pour garantir un ordre d'exécution correct, ou pour contourner les limitations des tables de mutation.
Une autre limitation importante de l'isolation des transactions apparaît : les modifications apportées à une ligne peuvent ne pas se propager intégralement, car cette ligne est référencée par des données que votre transaction ne peut pas « voir », et sur lesquelles elle ne peut donc pas se propager. Par exemple : pendant qu'une transaction tente de renuméroter un compte client, une autre transaction tente de créer une nouvelle facture pour ce même client. Bien qu'une règle CASCADE puisse corriger toutes les lignes de facture accessibles à votre transaction afin de les maintenir cohérentes avec la ligne client renumérotée, elle ne pourra pas accéder à l'autre transaction pour corriger les données qui s'y trouvent. En effet, la base de données ne pouvant garantir la cohérence des données lors de la validation des deux transactions, l'une d'elles sera annulée (souvent selon le principe du premier arrivé, premier servi).
CRÉER TABLE Fournisseur ( NuméroFournisseur INTEGER NOT NULL , Nom VARCHAR ( 20 ) NOT NULL , Adresse VARCHAR ( 50 ) NOT NULL , CONTRAINTE fournisseur_pk CLÉ PRIMAIRE ( NuméroFournisseur ), CONTRAINTE valeur_numéro CHECK ( NuméroFournisseur > 0 ) )CRÉER TABLE Facture ( NuméroFacture INTEGER NOT NULL , Texte VARCHAR ( 4096 ), NuméroFournisseur INTEGER NOT NULL , CONSTRAINT clé_facture PRIMAIRE ( NuméroFacture ) , CONSTRAINT valeur_numéro CHECK ( NuméroFacture > 0 ), CONSTRAINT clé_fournisseur FOREIGN KEY ( NuméroFournisseur ) REFERENCES Fournisseur ( NuméroFournisseur ) ON UPDATE CASCADE ON DELETE RESTRICT )