17 - Clés primaires et étrangères

Les clés primaires ont déjà été introduites dans le chapitre de création des tables. Je vous avais alors donné la définition suivante :

La clé primaire d'une table est une contrainte d'unicité, composée d'une ou plusieurs colonnes, et qui permet d'identifier de manière unique chaque ligne de la table.

Examinons plus en détail cette définition.

• Contrainte d'unicité : ceci ressemble fort à un index UNIQUE.
• Composée d'une ou plusieurs colonnes : comme les index, les clés peuvent donc être composites.
• Permet d'identifier chaque ligne de manière unique : dans ce cas, une clé primaire ne peut pas être NULL.

Ces quelques considérations résument très bien l'essence des clés primaires. En gros, une clé primaire est un index UNIQUE sur une colonne qui ne peut pas être NULL.
D'ailleurs, vous savez déjà que l'on définit une clé primaire grâce aux mots-clés PRIMARY KEY. Or, nous avons vu dans le précédent chapitre que KEY s'utilise pour définir un index. Par conséquent, lorsque vous définissez une clé primaire, pas besoin de définir en plus un index sur la (les) colonne(s) qui compose(nt) celle-ci, c'est déjà fait ! Et pas besoin non plus de rajouter une contrainte NOT NULL
Pour le dire différemment, une contrainte de clé primaire est donc une combinaison de deux des contraintes que nous avons vues jusqu'à présent : UNIQUE et NOT NULL.

Choix de la clé primaire

Le choix d'une clé primaire est une étape importante dans la conception d'une table. Ce n'est pas parce que vous avez l'impression qu'une colonne, ou un groupe de colonnes, pourrait faire une bonne clé primaire que c'est le cas. Reprenons l'exemple d'une table Client, qui contient le nom, le prénom, la date de naissance et l'email des clients d'une société.

Chaque client a bien sûr un nom et un prénom. Est-ce que (nom, prenom) ferait une bonne clé primaire ? Non bien sûr : il est évident ici que vous risquez des doublons.
Et si on ajoute la date de naissance ? Les chances de doublons sont alors quasi nulles. Mais quasi nul, ce n'est pas nul… Qu'arrivera-t-il le jour où vous voyez débarquer un client qui a les mêmes nom et prénom qu'un autre, et qui est né le même jour ? On refait toute la base de données ? Non, bien sûr.
Et l'email alors ? Il est impossible que deux personnes aient la même adresse email, donc la contrainte d'unicité est respectée. Par contre, tout le monde n'est pas obligé d'avoir une adresse email. Difficile donc de mettre une contrainte NOT NULL sur cette colonne.

Par conséquent, on est bien souvent obligé d'ajouter une colonne pour jouer le rôle de la clé primaire. C'est cette fameuse colonne id, auto-incrémentée que nous avons déjà vue pour la table Animal.

Il y a une autre raison d'utiliser une colonne spéciale auto-incrémentée, de type INT (ou un de ses dérivés) pour la clé primaire. En effet, si l'on définit une clé primaire, c'est en partie dans le but d'utiliser au maximum cette clé pour faire des recherches dans la table. Bien sûr, parfois ce n'est pas possible, parfois vous ne connaissez pas l'id du client, et vous êtes obligés de faire une recherche par nom. Cependant, vous verrez bientôt que les clés primaires peuvent servir à faire des recherches de manièreindirecte sur la table. Du coup, comme les recherches sont beaucoup plus rapides sur des nombres que sur des textes, il est souvent intéressant d'avoir une clé primaire composée de colonnes de type INT.

Enfin, il y a également l'argument de l'auto-incrémentation. Si vous devez remplir vous-mêmes la colonne de la clé primaire, étant donné que vous êtes humains (comment ça pas du tout ?  ), vous risquez de faire une erreur. Avec une clé primaire auto-incrémentée, vous ne risquez rien : MySQL fait tout pour vous. De plus, on ne peut définir une colonne comme auto-incrémentée que si elle est de type INT et qu'il existe un index dessus. Dans le cas d'une clé primaire auto-incrémentée, on définit généralement la colonne comme un entier UNSIGNED, comme on l'a fait pour la table Animal.

Il peut bien sûr n'y avoir qu'une seule clé primaire par table. De même, une seule colonne peut être auto-incrémentée (la clé primaire en général).

PRIMARY KEY or not PRIMARY KEY

Je me dois de vous dire que d'un point de vue technique, avoir une clé primaire sur chaque table n'est pas obligatoire. Vous pourriez travailler toute votre vie sur une base de données sans aucune clé primaire, et ne jamais voir un message d'erreur à ce propos.
Cependant, d'un point de vue conceptuel, ce serait une grave erreur. Ce n'est pas le propos de ce tutoriel que de vous enseigner les étapes de conception d'une base de données mais, s'il vous plaît, pensez à mettre une clé primaire sur chacune de vos tables. Si l'utilité n'en est pas complètement évidente pour vous pour le moment, elle devrait le devenir au fur et à mesure de votre lecture.

Création d'une clé primaire

La création des clés primaires étant extrêmement semblable à la création d'index simples, j'espère que vous me pardonnerez si je ne détaille pas trop mes explications. 

Donc, à nouveau, la clé primaire peut être créée en même temps que la table, ou par la suite.

Lors de la création de la table

On peut donc préciser PRIMARY KEY dans la description de la colonne qui doit devenir la clé primaire (pas de clé composite dans ce cas) :

1CREATE TABLE [IF NOT EXISTS] Nom_table (

2    colonne1 description_colonne1 PRIMARY KEY [,

3    colonne2 description_colonne2,

4    colonne3 description_colonne3,

5    ...,]

6)

7[ENGINE=moteur];

Exemple : création de la table Animal en donnant la clé primaire dans la description de la colonne.

1CREATE TABLE Animal (

2    id SMALLINT AUTO_INCREMENT PRIMARY KEY,

3    espece VARCHAR(40) NOT NULL,

4    sexe CHAR(1),

5    date_naissance DATETIME NOT NULL,

6    nom VARCHAR(30),

7    commentaires TEXT

8)

9ENGINE=InnoDB;

Ou bien, on ajoute la clé à la suite des colonnes.

1CREATE TABLE [IF NOT EXISTS] Nom_table (

2    colonne1 description_colonne1 [,

3    colonne2 description_colonne2,

4    colonne3 description_colonne3,

5    ...],

6    [CONSTRAINT [symbole_contrainte]] PRIMARY KEY (colonne_pk1 [, colonne_pk2, ...])  -- comme pour les index UNIQUE, CONSTRAINT est facultatif

7)

8[ENGINE=moteur];

C'est ce que nous avions fait d'ailleurs pour la table Animal. Cette méthode permet bien sûr la création d'une clé composite (avec plusieurs colonnes).

Exemple : création de Animal.

1CREATE TABLE Animal (

2    id SMALLINT AUTO_INCREMENT,

3    espece VARCHAR(40) NOT NULL,

4    sexe CHAR(1),

5    date_naissance DATETIME NOT NULL,

6    nom VARCHAR(30),

7    commentaires TEXT,

8    PRIMARY KEY (id)                 

9)

10ENGINE=InnoDB;

Après création de la table

On peut toujours utiliser ALTER TABLE. Par contre, CREATE INDEX n'est pas utilisable pour les clés primaires.

Si vous créez une clé primaire sur une table existante, assurez-vous que la (les) colonne(s) où vous souhaitez l'ajouter ne contienne(nt) pas NULL.

1ALTER TABLE nom_table

2ADD [CONSTRAINT [symbole_contrainte]] PRIMARY KEY (colonne_pk1 [, colonne_pk2, ...]);

Suppression de la clé primaire

1ALTER TABLE nom_table

2DROP PRIMARY KEY

Pas besoin de préciser de quelle clé il s'agit, puisqu'il ne peut y en avoir qu'une seule par table !

Reprenons l'exemple dans lequel on a une table Client et une table Commande. Dans la tableCommande, on a une colonne qui contient une référence au client. Ici, le client numéro 3, M. Nicolas Jacques, a donc passé une commande de trois tubes de colle, tandis que Mme Marie Malherbe (cliente numéro 2) a passé deux commandes, pour du papier et des ciseaux.

C'est bien joli, mais que se passe-t-il si M. Hadrien Piroux passe une commande de 15 tubes de colle, et qu'à l'insertion dans la table Commande, votre doigt dérape et met 45 comme numéro de client ? C'est l'horreur ! Vous avez dans votre base de données une commande passée par un client inexistant, et vous passez votre après-midi du lendemain à vérifier tous vos bons de commande de la veille pour retrouver qui a commandé ces 15 tubes de colle.
Magnifique perte de temps !

Ce serait quand même sympathique si, à l'insertion d'une ligne dans la table Commande, un gentil petit lutin allait vérifier que le numéro de client indiqué correspond bien à quelque chose dans la tableClient, non ?
Ce lutin, ou plutôt cette lutine, existe ! Elle s'appelle "clé étrangère".

Par conséquent, si vous créez une clé étrangère sur la colonne client de la table Commande, en lui donnant comme référence la colonne numero de la table Client, MySQL ne vous laissera plus jamais insérer un numéro de client inexistant dans la table Commande. Il s'agit bien d'une contrainte !

Avant d'entamer une danse de joie, parce que quand même le SQL c'est génial, restez concentrés cinq minutes, le temps de lire et retenir quelques points importants.

• Comme pour les index et les clés primaires, il est possible de créer des clés étrangères composites.
• Lorsque vous créez une clé étrangère sur une colonne (ou un groupe de colonnes) – la colonneclient de Commande dans notre exemple –, un index est automatiquement ajouté sur celle-ci (ou sur le groupe).
• Par contre, la colonne (le groupe de colonnes) qui sert de référence - la colonne numero de Client- doit déjà posséder un index (où être clé primaire bien sûr).
• La colonne (ou le groupe de colonnes) sur laquelle (lequel) la clé est créée doit être exactementdu même type que la colonne (le groupe de colonnes) qu'elle (il) référence. Cela implique qu'en cas de clé composite, il faut le même nombre de colonnes dans la clé et la référence. Donc, sinumero (dans Client) est un INT UNSIGNED, client (dans Commande) doit être de type INT UNSIGNED aussi.
• Tous les moteurs de table ne permettent pas l'utilisation des clés étrangères. Par exemple, MyISAM ne le permet pas, contrairement à InnoDB.

Création

Une clé étrangère est un peu plus complexe à créer qu'un index ou une clé primaire, puisqu'il faut deux éléments :

• la ou les colonnes sur laquelle (lesquelles) on crée la clé - on utilise FOREIGN KEY ;
• la ou les colonnes qui va (vont) servir de référence - on utilise REFERENCES.

Lors de la création de la table

Du fait de la présence de deux paramètres, une clé étrangère ne peut que s'ajouter à la suite des colonnes, et pas directement dans la description d'une colonne. Par ailleurs, je vous conseille ici de créer explicitement une contrainte (grâce au mot-clé CONSTRAINT) et de lui donner un symbole. En effet, pour les index, on pouvait utiliser leur nom pour les identifier ; pour les clés primaires, le nom de la table suffisait puisqu'il n'y en a qu'une par table. Par contre, pour différencier facilement les clés étrangères d'une table, il est utile de leur donner un nom, à travers la contrainte associée.
À nouveau, je respecte certaines conventions de nommage : mes clés étrangères ont des noms commençant par fk (pour FOREIGN KEY), suivi du nom de la colonne dans la table puis (si elle s'appelle différemment) du nom de la colonne de référence, le tout séparé par des _ (fk_client_numero par exemple).

1CREATE TABLE [IF NOT EXISTS] Nom_table (

2    colonne1 description_colonne1,

3    [colonne2 description_colonne2,

4    colonne3 description_colonne3,

5    ...,]

6    [ [CONSTRAINT [symbole_contrainte]]  FOREIGN KEY (colonne(s)_clé_étrangère) REFERENCES table_référence (colonne(s)_référence)]

7)

8[ENGINE=moteur];

Donc si on imagine les tables Client et Commande, pour créer la table Commande avec une clé étrangère ayant pour référence la colonne numero de la table Client, on utilisera :

1CREATE TABLE Commande (

2    numero INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

3    client INT UNSIGNED NOT NULL,

4    produit VARCHAR(40),

5    quantite SMALLINT DEFAULT 1,

6    CONSTRAINT fk_client_numero          -- On donne un nom à notre clé

7        FOREIGN KEY (client)             -- Colonne sur laquelle on crée la clé

8        REFERENCES Client(numero)        -- Colonne de référence

9)

10ENGINE=InnoDB;                          -- MyISAM interdit, je le rappelle encore une fois !

Après création de la table

Tout comme pour les clés primaires, pour créer une clé étrangère après création de la table, il faut utiliser ALTER TABLE.

1ALTER TABLE Commande

2ADD CONSTRAINT fk_client_numero FOREIGN KEY client REFERENCES Client(numero);

Suppression d'une clé étrangère

Il peut y avoir plusieurs clés étrangères par table. Par conséquent, lors d'une suppression il faut identifier la clé à détruire. Cela se fait grâce au symbole de la contrainte.

1ALTER TABLE nom_table

2DROP FOREIGN KEY symbole_contrainte