Le modèle relationnel et les clés (primaire, étrangère)

Clé primaire soulignée, clé étrangère précédée d'un # : derrière ces conventions se cache toute la logique du modèle relationnel, au programme de l'UE 8 du DCG. Savoir identifier la clé primaire et la clé étrangère d'un schéma, comprendre leur rôle et les traduire en SQL est exigé dans pratiquement tous les sujets d'examen. Faisons le point.

Le modèle relationnel : des tables reliées par des clés

Le modèle relationnel, formalisé par Edgar F. Codd en 1970, fournit un cadre mathématique rigoureux pour organiser les données. Tout y est table - on dit aussi « relation » : chaque ligne est un enregistrement (un client précis, une commande précise), chaque colonne est un attribut (le nom du client, la date de la commande).

La force du modèle ne vient pas des tables elles-mêmes, mais des liens entre elles. Ces liens reposent sur deux notions complémentaires : la clé primaire et la clé étrangère.

La clé primaire : identifier chaque ligne de façon unique

La clé primaire (PK, Primary Key) est un attribut - ou un ensemble d'attributs - qui identifie de manière unique chaque enregistrement d'une table. Exemples : NumeroClient, CodeProduit, ISBN.

Deux exigences absolues découlent de la contrainte d'entité : la clé primaire ne peut jamais être nulle (NULL), et chacune de ses valeurs est unique dans la table - jamais de doublons. Sans clé primaire, impossible de distinguer deux lignes avec certitude.

La clé primaire peut être composée de plusieurs attributs : c'est le cas dans les tables d'association, où elle concatène les clés des deux entités reliées (par exemple (#NumCommande, #CodeProduit) dans une table de lignes de commande), ou dans les entités faibles identifiées relativement à une autre (la chambre 101 n'étant unique qu'au sein d'un hôtel donné, la clé de CHAMBRE est (NumHotel, NumChambre)).

Conseil de conception : évitez les clés primaires à signification métier (numéro de sécurité sociale, nom). Ces valeurs peuvent changer ou comporter des erreurs ; un identifiant technique auto-incrémenté est plus sûr.

La clé étrangère : le mécanisme qui relie les tables

La clé étrangère (FK, Foreign Key) est un attribut d'une table qui fait référence à la clé primaire d'une autre table. C'est le mécanisme qui crée le lien entre deux tables dans un modèle relationnel.

Exemple : dans la table COMMANDE, l'attribut #NumClient référence la clé primaire de CLIENT. Chaque commande « connaît » ainsi son client. La règle d'intégrité référentielle impose que toute valeur de clé étrangère corresponde à une valeur existante de la clé primaire référencée - ou soit nulle si l'association est optionnelle. Pas de commande orpheline sans client identifié.

Cas particulier : une clé étrangère peut référencer la clé primaire de sa propre table. C'est l'auto-référence, issue d'une association réflexive : EMPLOYE(NumEmploye, Nom, Poste, #NumDirecteur), où #NumDirecteur pointe vers un autre employé de la même table.

De la notation en intension au SQL

Dans un schéma relationnel écrit en notation en intension, la clé primaire est soulignée et chaque clé étrangère est précédée du symbole #. La traduction en SQL est directe :

chaque table = une instruction CREATE TABLE ;
la clé primaire soulignée = PRIMARY KEY ;
chaque # = FOREIGN KEY ... REFERENCES ;
NOT NULL interdit les valeurs vides pour un champ obligatoire.

Exemple :

CREATE TABLE CLIENT (
    NumClient INT PRIMARY KEY,
    NomClient VARCHAR(50) NOT NULL,
    Email VARCHAR(100)
);

CREATE TABLE COMMANDE (
    NumCommande INT PRIMARY KEY,
    DateCommande DATE,
    NumClient INT,
    FOREIGN KEY (NumClient) REFERENCES CLIENT(NumClient)
);

Attention à l'ordre de création : la table référencée (CLIENT) doit être créée avant la table qui la référence (COMMANDE).

Un exemple concret : le site e-commerce

Cas type très fréquent à l'examen. Un site vend des produits classés en catégories ; les clients passent des commandes livrées à une adresse ; un client peut avoir plusieurs adresses. Le schéma :

CATEGORIE(CodeCat, LibelleCat)
PRODUIT(CodeProd, NomProd, Prix, #CodeCat)
CLIENT(NumClient, Nom, Prenom, Email)
ADRESSE(NumAdresse, Rue, CP, Ville, #NumClient)
COMMANDE(NumCommande, DateCommande, Statut, #NumClient, #NumAdresse)
LIGNECOMMANDE(#NumCommande, #CodeProd, Quantite, PrixUnitaire)

Chaque clé étrangère raconte une règle de gestion : #CodeCat rattache le produit à sa catégorie unique ; #NumClient dans ADRESSE rattache chaque adresse à son propriétaire ; COMMANDE porte deux clés étrangères, vers le client qui l'a passée et vers l'adresse de livraison - c'est parfaitement normal, une table peut référencer plusieurs autres tables. Enfin LIGNECOMMANDE, table d'association entre COMMANDE et PRODUIT, a une clé primaire composée de deux clés étrangères.

Les erreurs fréquentes

Croire que la clé étrangère identifie de façon unique un enregistrement : c'est le rôle de la clé primaire. La clé étrangère, elle, peut se répéter (plusieurs commandes du même client).
Accepter des doublons ou des NULL dans la clé primaire : c'est interdit par définition (contrainte d'entité).
Écrire une clé étrangère qui ne référence rien : toute valeur de FK doit exister dans la table référencée (intégrité référentielle).
Ne pas repérer les tables d'association à la lecture d'un MLD : une table dont la clé primaire est composée de deux clés étrangères trahit toujours une association n,n entre deux entités.

FAQ

Une table peut-elle avoir plusieurs clés étrangères ?

Oui, autant que de tables qu'elle référence. La table COMMANDE du e-commerce en porte deux : #NumClient (qui a commandé) et #NumAdresse (où livrer). En revanche, une table n'a qu'une seule clé primaire - éventuellement composée de plusieurs attributs.

Comment retrouver les associations du MCD à partir d'un MLD ?

Repérez les clés étrangères : une FK simple dans une table correspond à une association 1,n (le côté « n » est la table qui porte la FK). Une table dont la PK concatène deux FK correspond à une association n,n entre les deux tables référencées.

Un attribut peut-il être à la fois clé primaire et clé étrangère ?

Oui. Dans une table d'association, chaque composant de la clé primaire est aussi une clé étrangère. De même, dans une entité faible, la clé étrangère vers l'entité forte fait partie de la clé primaire composite.

Entraînez-vous

On vous donne le MLD suivant :

FOURNISSEUR(CodeFourn, NomFourn, VilleFourn)
PRODUIT(CodeProd, LibelleProd, PrixHT, #CodeFourn)
COMMANDE(NumCommande, DateCommande, #CodeClient)
CLIENT(CodeClient, NomClient, VilleClient)
LIGNECOMMANDE(#NumCommande, #CodeProd, Quantite)

Identifiez les clés primaires et les clés étrangères de chaque table.
Reconstituez les associations du MCD et leurs cardinalités.
Un fournisseur peut-il fournir plusieurs produits ? Justifiez à partir du schéma.

Afficher le corrigé

1. Clés. FOURNISSEUR : PK = CodeFourn. PRODUIT : PK = CodeProd ; FK = #CodeFourn vers FOURNISSEUR. CLIENT : PK = CodeClient. COMMANDE : PK = NumCommande ; FK = #CodeClient vers CLIENT. LIGNECOMMANDE : PK composée = (#NumCommande, #CodeProd) ; deux FK, vers COMMANDE et vers PRODUIT.

2. Associations reconstituées. FOURNISSEUR (1,n) - PRODUIT : un fournisseur fournit plusieurs produits, chaque produit a un seul fournisseur (FK simple dans PRODUIT). CLIENT (1,n) - COMMANDE : un client passe plusieurs commandes (FK simple dans COMMANDE). COMMANDE (n,n) - PRODUIT via LIGNECOMMANDE : une commande contient plusieurs produits et un produit figure dans plusieurs commandes (table d'association à PK composée de deux FK, portant l'attribut Quantite).

3. Oui. La clé étrangère #CodeFourn se trouve dans PRODUIT, c'est-à-dire du côté « n » de l'association : rien n'empêche plusieurs lignes de PRODUIT de porter le même CodeFourn. Chaque produit connaît son unique fournisseur, mais un fournisseur peut être référencé par autant de produits que nécessaire. Si la FK avait été placée dans FOURNISSEUR, chaque fournisseur n'aurait pu fournir qu'un seul produit - c'est exactement l'erreur de sens à éviter.