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 table Client, 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 colonne client 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 exactement du 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, si numero (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).
Partie 2 : Index, jointures et sous-requêtes
92/414
www.openclassrooms.com
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 table Client, 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 colonne client 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 exactement du 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, si numero (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).
Partie 2 : Index, jointures et sous-requêtes
92/414
www.openclassrooms.com