Clés étrangères et relations

Stocker des informations

Imaginons maintenant que l'on souhaite stocker des informations sur quel étudiant suit quel cours. On pourrait faire quelque chose qui ressemble à ceci :

Relations entre tables

Il faut découper nos informations entre plusieurs tables et faire des références entre les tables. On référence alors la clé primaire d'une des tables dans l'autre table.

C'est ce que l'on appelle une clé étrangère (FOREIGN KEY).

On remarque la direction de la flèche, de la table appelée « enfant » vers la table « parent ».

On ajoute dans la table enfant un champ enseignant et une annotation de clé étrangère. Donc ici chaque cours possède un enseignant.

erDiagram  
    enseignants ||--o{ cours : "enseigne" 
enseignants {
        NUMERIC(8) code_employe PK
        VARCHAR(255) nom
        VARCHAR(255) prenom
        CHAR(9) num_assurance_sociale
        TINYINT anciennete
    }
cours {
        INTEGER cours_id PK "AUTO_INCREMENT"
        VARCHAR(255) nom
        CHAR(10) sigle
        TINYINT duree "=60"
        TINYINT nombre_semaine "=15"
        NUMERIC(8) enseignant FK
    }    

Importances des relations

On trouve rarement une table isolée dans un modèle de base de données (table en relation avec aucune autre table).

Pour faire une analogie, on pourrait comporarer une table isolée à du code qui n'est pas référencé (fonction qui n'est jamais appelée) dans le domaine de la programmation.

Clé étrangère SQL

Pour indiquer une clé étrangère, on ajoute la contrainte suivante dans la requête de création de la table.

FOREIGN KEY (nom_colonne) REFERENCES table_parent(cle_primaire)

Exemple avec la table cours

CREATE TABLE cours (  
    id INTEGER PRIMARY KEY AUTO_INCREMENT,  
    sigle CHAR (11),  
    duree TINYINT, 
    nom VARCHAR(255),
    enseignant_id NUMERIC(8),
    FOREIGN KEY (enseignant_id) REFERENCES enseignants (id));

Ici rien ne change dans la création de la table enseignants (la table ne sait pas qu'elle est utilisée comme clé étrangère ailleurs).

Suppression de tables qui contiennent des relations

erDiagram  
    enseignants ||--o{ cours : "enseigne" 
enseignants {
        NUMERIC(8) code_employe PK
        VARCHAR(255) nom
        VARCHAR(255) prenom
        CHAR(9) num_assurance_sociale
        TINYINT anciennete
    }
cours {
        INTEGER cours_id PK "AUTO_INCREMENT"
        VARCHAR(255) nom
        CHAR(10) sigle
        TINYINT duree "=60"
        TINYINT nombre_semaine "=15"
        NUMERIC(8) enseignant FK
    } 

Peut-on supprimer la table enseignants sans supprimer la table cours? Pourquoi?

Si l'on pouvait faire la suppression, la table cours aurait une colonne qui contiendrait des clés d'une table inexistante.

Ordre de création

Afin d'ajouter les contraintes de clés étrangères, la table parent doit être créée avant la table enfants.

Autrement, vous aurez une erreur SQL disant que votre contrainte FOREIGN KEY référence une table inexistante.

--- Exercice 1.5.1 ---

Proposez un modèle de base de données illustrant qu'un enseignant est responsable d'un programme.

Implémentez la base de données correspondant à votre modèle.

Tables d'association

Les clés étrangères permettent de référer un enregistrement dans une autre table. Qu'arrive-t-il dans la situation suivante:

Une BD permet de gérer les inscriptions des étudiants à leurs cours. Comment représenter le fait qu'un étudiant puisse s'inscrire à plusieurs cours et qu'à un même cours, plusieurs étudiants puissent s'inscrire ?

On ajoute une table dont le rôle dont les enregistrements représentent chaque relation.

Par exemple, on ajouterait une table inscriptions pour représenter notre association. Comme inscriptions est créée spécialement pour représenter une association, elle est appelée table d'association.

On voit ici que la table inscriptions assure l'association entre cours et etudiants.

On peut voir que deux tables sont associées si l'on peut suivre avec notre doigt d'une table à l'autre en empruntant les flèches comme des chemins (ici le sens des flèches n'a pas d'importance).

erDiagram  
    etudiants ||--o{ inscriptions : " " 
etudiants {
        NUMERIC(7) code_etudiant PK 
        VARCHAR(255) nom
        VARCHAR(255) prenom
        YEAR annee_admission
        DATETIME date_naissance
        VARCHAR(6) code_programme FK
     }
    inscriptions }o--|| cours : " "
inscriptions { 
        NUMERIC(7) code_etudiant PK "FK"
        INTEGER groupe_id PK "FK"
    }
cours {
        INTEGER cours_id PK "AUTO_INCREMENT"
        VARCHAR(255) nom
        CHAR(10) sigle
        TINYINT duree "=60"
        TINYINT nombre_semaine "=15"
        NUMERIC(8) enseignant FK
    }    

Mais... un instant!

Dans la table inscriptions la clé primaire est-elle vraiment composée de deux colonnes?

Oui, c'est possible: c'est appelé une clé composée.

erDiagram   
    etudiants ||--o{ inscriptions : " " 
etudiants {
        NUMERIC(7) code_etudiant PK 
        VARCHAR(255) nom
        VARCHAR(255) prenom
        YEAR annee_admission
        DATETIME date_naissance
        VARCHAR(6) code_programme FK
     }
    inscriptions }o--|| cours : " "
inscriptions { 
        NUMERIC(7) code_etudiant PK "FK"
        INTEGER groupe_id PK "FK"
    }
cours {
        INTEGER cours_id PK "AUTO_INCREMENT"
        VARCHAR(255) nom
        CHAR(10) sigle
        TINYINT duree "=60"
        TINYINT nombre_semaine "=15"
        NUMERIC(8) enseignant FK
    }   

Clé composée

Pour indiquer une clé composée dans une base de données, nous devrons utiliser la notation de contrainte.

Après avoir déclaré les colonnes, on indique

PRIMARY KEY (nom_colonne1, nom_colonne2, … )

Bonne écriture :

CREATE TABLE inscriptions(
    cours_id INTEGER,
    etudiants_id INTEGER,
    PRIMARY KEY (etudiant_id, cours_id), #on fait la clé en 1 étape
    FOREIGN KEY (etudiant_id) REFERENCES etudiants (id),
    FOREIGN KEY (cours_id) REFERENCES cours (id));

Voici la gaffe "semi-logique".

CREATE TABLE inscriptions(
    etudiant_id INTEGER PRIMARY KEY, 
    cours_id INTEGER PRIMARY KEY,
    FOREIGN KEY (etudiant_id) REFERENCES etudiants (id),
    FOREIGN KEY (cours_id) REFERENCES cours (id));

Notation de contraintes

La notation contrainte de PRIMARY KEY s'utilise aussi en présence d'une clé simple (clé comportant une seule colonne).

La notation vue précédemment constitue un raccourci intéressant lorsqu'on travaille.

On revient plus en détail sur les contraintes dans le Chapitre 4 - Assurer l'intégrité des données.