Fiche 7 sur 12

Les clés étrangères

Relier les tables pour de bon, et protéger la cohérence des données.

Après cette fiche, vous saurez :

déclarer une clé étrangère
comprendre ce que la contrainte empêche
choisir le comportement en cas de suppression
créer un index pour accélérer les recherches

1Le problème sans clé étrangère

À la fiche 6, nous avons créé des colonnes num_auteur qui sont de simples entiers. Rien ne les relie vraiment.

INSERT INTO livres (titre, num_auteur) VALUES ('Germinal', 9999);

PostgreSQL accepte. Le livre pointe vers l'auteur 9999, qui n'existe pas. La base contient maintenant une donnée incohérente, et personne ne le sait.

À retenir

Une clé étrangère transforme la colonne en vraie liaison surveillée.

PostgreSQL vérifiera à chaque insertion que la valeur existe bien dans l'autre table. On appelle ça l'intégrité référentielle.

2Déclarer une clé étrangère

Écriture courte

CREATE TABLE livres (
    num_livre   INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    titre       VARCHAR(150) NOT NULL,
    annee       INTEGER,
    num_auteur  INTEGER REFERENCES auteurs(num_auteur)
);

REFERENCES auteurs(num_auteur) : cette colonne ne peut contenir que des valeurs qui existent dans auteurs.num_auteur.

Écriture nommée, recommandée

CREATE TABLE livres (
    num_livre   INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    titre       VARCHAR(150) NOT NULL,
    annee       INTEGER,
    num_auteur  INTEGER,

    CONSTRAINT fk_livres_auteur
        FOREIGN KEY (num_auteur)
        REFERENCES auteurs(num_auteur)
);

C'est plus long, mais la contrainte porte un nom. Quand PostgreSQL refusera une insertion, il citera fk_livres_auteur au lieu d'un nom automatique illisible.

À retenir

Convention de nommage : fk_ + table de départ + _ + table visée.

fk_livres_auteur, fk_emprunts_lecteur. Simple, et tout le monde s'y retrouve.

Ajouter une clé étrangère après coup

Si vos tables existent déjà :

ALTER TABLE livres
    ADD CONSTRAINT fk_livres_auteur
    FOREIGN KEY (num_auteur)
    REFERENCES auteurs(num_auteur);

Attention

Deux conditions pour que ça marche.

La colonne visée doit être une clé primaire ou UNIQUE. On ne peut pas pointer vers n'importe quoi.
Les données déjà présentes doivent respecter la contrainte. Si un livre pointe vers l'auteur 9999, l'ALTER TABLE échouera.

3Ce que la contrainte empêche

Une fois la clé étrangère en place, PostgreSQL bloque deux choses.

Insérer une valeur qui n'existe pas

INSERT INTO livres (titre, num_auteur) VALUES ('Germinal', 9999);

ERROR: insert or update on table "livres" violates foreign key constraint "fk_livres_auteur"
DETAIL: Key (num_auteur)=(9999) is not present in table "auteurs".

Supprimer une ligne encore référencée

DELETE FROM auteurs WHERE num_auteur = 2;

ERROR: update or delete on table "auteurs" violates foreign key constraint "fk_livres_auteur" on table "livres"
DETAIL: Key (num_auteur)=(2) is still referenced from table "livres".

Zola a des livres dans la base. PostgreSQL refuse de le supprimer, parce que ses livres deviendraient orphelins.

À retenir

Ces messages d'erreur sont vos amis.

Ils sont longs, mais ils disent exactement : quelle table, quelle contrainte, quelle valeur pose problème. Prenez le temps de les lire au lieu de les subir.

La ligne DETAIL est souvent la plus utile.

4Choisir ce qui se passe en cas de suppression

Le refus n'est pas toujours ce qu'on veut. On peut préciser le comportement.

Option	Effet quand on supprime la ligne visée
`ON DELETE RESTRICT`	Refuse la suppression. C'est le comportement par défaut.
`ON DELETE CASCADE`	Supprime aussi les lignes qui pointaient vers elle.
`ON DELETE SET NULL`	Garde les lignes, mais vide la clé étrangère.

-- Supprimer un lecteur supprime aussi ses emprunts
CONSTRAINT fk_emprunts_lecteur
    FOREIGN KEY (num_lecteur)
    REFERENCES lecteurs(num_lecteur)
    ON DELETE CASCADE

Attention

CASCADE est dangereux. Réfléchissez avant de l'utiliser.

Supprimer un lecteur effacerait silencieusement tout son historique d'emprunts. Dans une bibliothèque, c'est probablement une perte de données comptables.

La règle simple :

CASCADE quand la ligne n'a aucun sens sans son parent. Une ligne de commande sans commande, par exemple.
RESTRICT partout ailleurs. C'est le défaut, et c'est bien.

5Le script complet de la bibliothèque

DROP TABLE IF EXISTS emprunts;
DROP TABLE IF EXISTS livres;
DROP TABLE IF EXISTS lecteurs;
DROP TABLE IF EXISTS auteurs;

CREATE TABLE auteurs (
    num_auteur   INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    nom          VARCHAR(50) NOT NULL,
    prenom       VARCHAR(50),
    nationalite  VARCHAR(40)
);

CREATE TABLE lecteurs (
    num_lecteur       INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    nom               VARCHAR(50) NOT NULL,
    prenom            VARCHAR(50) NOT NULL,
    adresse           VARCHAR(120),
    telephone         VARCHAR(20),
    date_inscription  DATE DEFAULT CURRENT_DATE
);

CREATE TABLE livres (
    num_livre   INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    titre       VARCHAR(150) NOT NULL,
    annee       INTEGER CHECK (annee > 1400),
    isbn        VARCHAR(20) UNIQUE,
    num_auteur  INTEGER,

    CONSTRAINT fk_livres_auteur
        FOREIGN KEY (num_auteur)
        REFERENCES auteurs(num_auteur)
);

CREATE TABLE emprunts (
    num_emprunt   INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    date_emprunt  DATE NOT NULL DEFAULT CURRENT_DATE,
    date_prevue   DATE NOT NULL,
    date_retour   DATE,
    num_lecteur   INTEGER NOT NULL,
    num_livre     INTEGER NOT NULL,

    CONSTRAINT fk_emprunts_lecteur
        FOREIGN KEY (num_lecteur)
        REFERENCES lecteurs(num_lecteur),

    CONSTRAINT fk_emprunts_livre
        FOREIGN KEY (num_livre)
        REFERENCES livres(num_livre)
);

Gardez ce script. Il vous servira à toutes les fiches suivantes.

6Les index

Un index sert à retrouver rapidement des lignes, comme l'index d'un livre papier permet de trouver une page sans lire tout l'ouvrage.

À retenir

Deux index sont créés automatiquement.

Sur chaque PRIMARY KEY
Sur chaque colonne UNIQUE

Vous n'avez rien à faire pour ceux-là. Le dossier d'analyse les prescrit, PostgreSQL les crée.

Les autres, c'est à vous de les décider. On en ajoute un quand une colonne sert souvent à filtrer ou à faire des jointures.

-- On cherche souvent les lecteurs par nom
CREATE INDEX idx_lecteurs_nom ON lecteurs(nom);

-- On fait souvent la jointure emprunts -> livres
CREATE INDEX idx_emprunts_livre ON emprunts(num_livre);

-- Supprimer un index
DROP INDEX idx_lecteurs_nom;

Attention

Un index n'est pas gratuit.

Il accélère les lectures, mais il ralentit les écritures : à chaque insertion, PostgreSQL doit aussi mettre l'index à jour. Et il occupe de la place.

Ne mettez donc pas d'index partout. On en ajoute quand une requête est lente, et qu'on a constaté qu'elle l'est — pas « au cas où ».

Sur les tables de quelques dizaines de lignes de ce cours, la différence est invisible. C'est sur des millions de lignes que ça change tout.

→Exercices

1Créer la base complèteRecopier

Exécutez le script complet de la section 5 dans votre base bibliotheque.
Vérifiez que les quatre tables sont là.
Dans pgAdmin, dépliez livres puis Constraints. Que voyez-vous ?
Dépliez Indexes. Combien y en a-t-il, et pourquoi ?

Solution

3. Les contraintes de livres

livres_pkey — la clé primaire, nommée automatiquement
livres_isbn_key — la contrainte UNIQUE sur l'ISBN
livres_annee_check — le CHECK sur l'année
fk_livres_auteur — notre clé étrangère, avec le nom qu'on lui a donné

Voyez la différence : les trois premières ont un nom généré par PostgreSQL, la quatrième porte le nôtre. C'est tout l'intérêt de nommer ses contraintes.

4. Les index

Deux index, que nous n'avons jamais demandés :

un sur num_livre, à cause du PRIMARY KEY ;
un sur isbn, à cause du UNIQUE.

PostgreSQL en a besoin pour vérifier l'unicité rapidement. Sans index, il devrait relire toute la table à chaque insertion.

Remarquez ce qui manque : il n'y a pas d'index sur num_auteur. Une clé étrangère n'en crée pas automatiquement. C'est souvent une bonne idée d'en ajouter un — nous le ferons à l'exercice 3.

2Provoquer les erreursModifier

Exécutez ces commandes une par une, dans l'ordre, et notez ce qui se passe.

-- 1
INSERT INTO auteurs (nom, prenom, nationalite)
VALUES ('Zola', 'Émile', 'Française');

-- 2
INSERT INTO livres (titre, annee, num_auteur)
VALUES ('Germinal', 1885, 1);

-- 3
INSERT INTO livres (titre, annee, num_auteur)
VALUES ('Un livre fantôme', 2020, 9999);

-- 4
DELETE FROM auteurs WHERE num_auteur = 1;

-- 5
INSERT INTO livres (titre, annee, num_auteur)
VALUES ('Livre sans auteur', 2020, NULL);

Pour chaque commande : réussit-elle ? Si non, pourquoi ? La commande 5 vous surprend-elle ?

Solution

1. Réussit. L'auteur est créé, et reçoit automatiquement le num_auteur 1.

2. Réussit. L'auteur 1 existe, la clé étrangère est satisfaite.

3. Échoue.

ERROR: insert or update on table "livres" violates foreign key constraint "fk_livres_auteur"
DETAIL: Key (num_auteur)=(9999) is not present in table "auteurs".

C'est exactement le problème du début de la fiche, maintenant bloqué.

4. Échoue.

ERROR: update or delete on table "auteurs" violates foreign key constraint "fk_livres_auteur" on table "livres"
DETAIL: Key (num_auteur)=(1) is still referenced from table "livres".

Zola a un livre. On ne peut pas le supprimer sans laisser Germinal orphelin.

5. Réussit — et c'est là que beaucoup se trompent.

Une clé étrangère interdit les valeurs inexistantes, mais elle autorise NULL. NULL veut dire « pas d'auteur connu », ce qui est une information parfaitement valable : pensez aux ouvrages anonymes.

Si vous voulez l'interdire, il faut ajouter NOT NULL :

num_auteur INTEGER NOT NULL,

C'est un choix de conception, pas un détail technique. Reprenez vos cardinalités de la fiche 3 : si vous aviez écrit 1,1 du côté auteur, alors NOT NULL s'impose. Si vous aviez écrit 0,1, il ne faut pas le mettre.

3Terminer votre baseCréer

Reprenez le script de la fiche 6, exercice 3.

Ajoutez toutes les clés étrangères, avec des contraintes nommées.
Décidez pour chacune s'il faut NOT NULL, en relisant vos cardinalités.
Choisissez le comportement ON DELETE et justifiez-le en commentaire.
Ajoutez un index sur au moins une clé étrangère.
Exécutez le script deux fois pour vérifier qu'il est rejouable.

Solution

DROP TABLE IF EXISTS consultations;
DROP TABLE IF EXISTS medecins;
DROP TABLE IF EXISTS patients;

CREATE TABLE patients (
    num_patient     INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    nom             VARCHAR(50) NOT NULL,
    prenom          VARCHAR(50) NOT NULL,
    date_naissance  DATE NOT NULL,
    telephone       VARCHAR(20),
    numero_national VARCHAR(15) UNIQUE
);

CREATE TABLE medecins (
    num_medecin   INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    nom           VARCHAR(50) NOT NULL,
    prenom        VARCHAR(50),
    specialite    VARCHAR(60) NOT NULL,
    numero_inami  VARCHAR(20) NOT NULL UNIQUE
);

CREATE TABLE consultations (
    num_consultation INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    date_consult     DATE NOT NULL DEFAULT CURRENT_DATE,
    heure            TIME,
    motif            TEXT,
    montant          NUMERIC(6,2) CHECK (montant >= 0),

    -- NOT NULL : une consultation sans patient n'existe pas
    num_patient      INTEGER NOT NULL,
    num_medecin      INTEGER NOT NULL,

    -- RESTRICT : on ne supprime pas un patient qui a un historique médical
    CONSTRAINT fk_consultations_patient
        FOREIGN KEY (num_patient)
        REFERENCES patients(num_patient)
        ON DELETE RESTRICT,

    CONSTRAINT fk_consultations_medecin
        FOREIGN KEY (num_medecin)
        REFERENCES medecins(num_medecin)
        ON DELETE RESTRICT
);

-- On liste souvent les consultations d'un patient
CREATE INDEX idx_consultations_patient ON consultations(num_patient);
CREATE INDEX idx_consultations_date ON consultations(date_consult);

Sur le choix de RESTRICT : en milieu médical, un dossier se conserve des années par obligation légale. CASCADE ferait disparaître tout l'historique d'un patient d'un seul clic. C'est exactement ce qu'il ne faut pas.

Sur les index : deux ont été ajoutés, et chacun se justifie par un usage réel. On liste les consultations d'un patient à chaque ouverture de dossier, et on sort la liste du jour chaque matin. Un index sur motif n'aurait aucun sens : personne ne cherche par motif.

Vérification : le script doit passer deux fois. Si la deuxième exécution échoue avec cannot drop table ... because other objects depend on it, c'est que vos DROP ne sont pas dans le bon ordre.

Je sais faire

Je sais déclarer une clé étrangère dans un CREATE TABLE.
Je sais l'ajouter après coup avec ALTER TABLE.
Je sais ce que ON DELETE CASCADE et ON DELETE RESTRICT font.
Je sais créer un index et dire à quoi il sert.

Une case qui coince ? Relisez la partie concernée avant de passer à la fiche suivante.

← Fiche 6 Fiche 8 : Ajouter, modifier, supprimer des données →