2 - Gérer les clés étrangères

Vous allez voir qui si une table contient des informations revenant régulièrement sur plusieurs de ses entrées, il est possible de créer une nouvelle table et de lier l'un des attributs de la vielle table à l'entrée de la nouvelle table.

Nous allons toujours travailler avec le vieux matériel. Comment donner les descriptions techniques de vieux ordinateurs faisant tourner les vieux jeux alors que ces informations apparaitraient très régulièrement dans la table actuelle.

Regardons ceci, melant les informations des jeux et des ordinateurs dans une même table :

On retrouve les informations sur les jeux de l'activité précédente mais j'ai rajouté des informations supplémentaires sur le support informatique utilisé : il y a toujours le nom mais également maintenant l'année de sortie et la mémoire vive (RAM) du modèle.

Alors, où est le problème ?

Dans la redondance : tous les jeux tournant en C64 vont contenir les informations sur le C64. Si je rajoute une image du C64, c'est encore pire : l'image sera stockée dans chacune des instances tournant avec ce matériel.

Si on veut afficher ceci à l'écran, le mieux est de créer une table 'jeux' ayant simplement un indicateur 'type d'ordinateur' : une clé extérieure.

On ira alors voir dans la table 'type d'ordinateur' pour trouver les informations liées au nom, à la mémoire et à la date de sortie.

1 - Création des tables

01° Créer une base de données à l'aide de phpmyadmin après avoir démarrer Apache et MySQL à l'aide de XAMP.

02° Importer (à l'aide du menu vertical) votre ancienne table de jeux (celle de l'activité précédente) ou télécharger la version présentée ci-dessous.

LA TABLE mes_jeux.sql à télécharger.

03° Créer une nouvelle table qu'on nommera par exemple support.

04° Créer simplement 4 attributs dans cette nouvelle table :

• Un idSupport (un SMALLINT unsigned, clé primaire pouvant s'incrémenter toute seule via Auto-Increment.
• Un TEXTVAR pour le nom de l'ordinateur.
• Un TEXTVAR pour la RAM.
• Un DATE pour la date_sortie.

05° Remplir la table avec les indications suivantes :

Et voilà le principe : dans la table 'jeux, nous n'allons placer que l'id du support : un simple entier qui prendra moins de place que les caractères décrivant la console. Pour connaitre les spécificités du support du jeu, il faut alors aller voir la table 'support' et chercher la bonne entrée à l'aide de l'identifiant !

Représenté comme cela, ça semble simple. En pratique, ce n'est pas compliqué mais il faut clairement expliquer à l'ordinateur ce que vous voulez faire. Et à la moindre imprécision, il perd ses repères et ne parvient plus à vous faire le lien entre les deux tables.

2 - Créer la clé étrangère

En anglais, clé étrangère se traduit par foreign key.

L'utilisation d'une clé étrangère garantit en quelque sorte que la référence donnée sur une table 1 correspond bien à une clé primaire d'une autre 2.

Dans notre exemple, on aura donc à vérifier que les valeurs contenus dans le champ idSupport de la table mes_jeux correspond bien à des valeurs existantes dans le champ idSupport de la table support.

Premier élément à bien comprendre : les deux champs doivent être exactement de même type : SMALLINT et UNSIGNED.

06° En utilisant l'onglet structure de la table mes_jeux, créer une nouvelle colonne qu'on nommera par exemple idSupport comme dans l'autre table. Le nom n'a pas besoin d'être identique mais c'est plus facile pour comprendre la liaison et éviter de se tromper. Il faudra créer une SMALLINT UNSIGNED de type INDEX. Ne placez pas par contre AI : nous allons créer un menu déroulant permettant de le remplir automatiquement. Inutile de fournir un nouveau nom à l'index : il prendra le nom du champ : idSupport.

Si vous allez voir dans la structure de la table mes_jeux, vous pouvez voir que le champ n'a rien de spécial pour l'instant : c'est un simple champ integer.

Voyons maintenant comment créer une liaison entre les choix donnés dans la table mes_jeux et l'id de la table support.

07° Sélectionner la table mes_jeux et utiliser Structure. Vous aurez une vue similaire à cela :

08° Cliquer sur la Vue Relationnelle.

C'est à partir de ce menu que nous allons pouvoir créer la liaison entre les entrées du menu déroulant et les valeurs rentrées dans la table support.

09° Après avoir cliquer sur Vue Relationnelle, créer une clé étrangère nommée FK_jeux_support en respectant les liaisions données ci-dessous :

Comme vous pouvez le voir, nous faisons le lien entre la colonne idSupport de la table mes_jeux et la colonne idSupport de la table support.

Après avoir validé, vous devriez obtenir ceci :

Le problème vient du fait que les éléments de mes_jeux sont déjà présents : dans la base de donnée, idSupport de mes_jeux contient des références 0 alors que idSupport de support ne contient que des références 1, 2, 3 mais pas de 0 à l'horizon.

Pour résoudre le problème, il faut

• soit remplir la table mes_jeux APRES avoir créer la liaison,
• soit rajouter un idSupport à 0 dans la table support,
• soit mettre la colonne de la table mes_jeux

Si la table mes_jeux était vide, il n'y aurait donc aucun problème mais là, nous tombons sur l'avantage des FOREIGN KEYS : on ne peut pas avoir de valeurs non existantes dans la colonne !

Or, il trouve 0. Il décide donc de ne pas créer la FOREIGN KEY, c'est aussi simple que cela.

Nous avons donc un problème d'organisation des tables : nous les avons créé de façon organique, alors qu'il faut normalement d'abord réflechir à la structure avant de la créer. Comment faire alors ?

Le plus facile est encore de créer une entrée d'id 0 dans la table support.

10° Créer une nouvelle entrée (onglet Insérer) dans support sans précise l'id mais en donnant non renseigné dans le champ 'nom'.

11° A l'aide de Editer, modifier l'ID de cette entrée pour l'imposer à 0 : 0 correspondra ainsi à une entrée non renseignée en terme de support.

12° Après avoir cliquer sur Vue Relationnelle, créer (une nouvelle fois) une clé étrangère nommée FK_jeux_support en respectant les liaisions données ci-dessous :

Il reste à voir maintenant ce que cela change lorsqu'on rajoute ou modifie des entrées.

13° Utiliser Editer pour placer la bonne valeur de clé étrangère dans les différents jeux :

14° Supprimer la colonne support dans la table mes_jeux : cette information est désormais accéssible via la clé étrangère du champ idSupport.

15° Si vous voulez récupérer la base de données contenant maintenant deux tables liées par la clé étrangère FG_jeux_support, il vous suffit d'utiliser EXPORTER en ayant bien sélectionner au pr

Je n'en parle pas ici, le but n'est pas de faire un cours réel sur les bases de données, mais il existe également un moyen de créer des contraintes entre tables : empêcher le champ d'une table de prendre une valeur inconnue sur l'autre table ou encore faire disparaitre les entrées contenant une entrée qu'on a supprimé sur l'autre table. Ainsi si on supprime un produit, on peut supprimer automatiquement toutes les commandes de ce produit. C'est pratique mais ça rajoute encore une couche de complexité.

3 - Jonction

Pour l'instant, nous avons réussi à imposer qu'un champ de 'mes_jeux' ne puisse prendre que les valeurs déjà enregistrées dans une autre table. C'est bien pratique pour remplir la table des jeux.

Par contre, si on regarde la table 'mes_jeux', on obtient ceci :

La dernière colonne est-elle claire ? Non. La clé étrangère (Foreign Key) permet de s'assurer que les valeurs données correspondent nécessairement à des supports connus, mais cela n'affiche pas le nom du support néanmoins...

Comment faire pour afficher le nom du support à côté du jeu ?

Nous allons devoir soulever un peu le moteur et commencer à taper des requêtes SQL. SQL stands for Structured Query Language. Oui oui, un peu d'anglais ne fait jamais de mal.

Les lignes de commandes SQL sont l'objet de l'activité 'Base de Données n°3'. Elles nous permettront de nous passer de phpMyAdmin et ainsi de commander la base de données directement avec des instructions. Le php ou Python, par exemple, pourront ainsi gérer l'ensemble de votre base de données.

Pour l'instant, voyons simplement comment associer les deux tables pour afficher les informations que nous voulons dans un tableau en utilisant les deux tables qui possèdent chacune une partie des données.

16° Cliquer sur l'icone SQL du menu vertical.

17° Lancer la commande SQL suivante :

Ce qui veut dire en SQL :

• Sélectionne tous les champs de toutes les entrées
• De la table mes_jeux
• En y rajoutant les champs de la table support si les champs idSupport des deux tables coïncident

En image :

On peut faire un peu mieux en ne demandant plus d'afficher les deux idSupport :

18° Lancer les commandes SQL précédentes.

En vous devriez obtenir une jolie et belle table :

Mais on peut encore faire mieux :

19° Lancer les commandes SQL précédentes. A quoi sert manifestement le WHERE ?

Et on peut encore rajouter des conditions avec le WHERE. On peut ainsi utiliser un AND ou un OR de façon à affiner la recherche.

20° Tenter d'afficher le tableau précédent mais uniquement avec les jeux de Plate-formes sortis sur C64.

Nous verrons dans l'activité suivante comment utiliser les commandes SQL pour créer, modifier et faire des recherches dans les tables.