Chapitre 4 :
Créer une base de données

Dans le chapitre "transformer un modèle conceptuel en modèle relationnel", nous avons vu qu'il existait deux types d'associations.

• Les associations "1 à plusieurs" qui se transformaient  en clés étrangères 
• Les associations "Plusieurs à plusieurs" qui se transformaient en relation avec comme clé primaire une concaténation de deux clés étrangères. 

Le but de ce chapitre est de vous montrer

• choisir le bon type et les bonnes options pour une colonne clé étrangère
• comment on indique au serveur sql qu'une colonne est une clé étrangère qui fait référence à une colonne clé primaire d'une autre table. 
• choisir ce que le serveur doit faire lorsqu'un utilisateur supprime ou modifie une valeur de la colonne clé primaire alors que cette valeur est utilisée dans la colonne cé étrangère

Le type d'une colonne clé étrangère

Une colonne clé étrangère contient des données qui correspondent à des données d'une colonne clé primaire. Il est donc nécessaire qu'une colonne clé étrangère ait le même type et le même attribut. 

Nous avons vu qu'une clé primaire doit avoir le type SERIAL qui est un raccourci pour le type BIGINT(20), l'attribut UNSIGNED, et les options NOT NULL, UNIQUE, et AUTO_INCREMENT

Les clés étrangères doivent être du type BIGINT 20 et  avoir l'attribut UNSIGNED. Par contre, il est impossible de mettre l'auto_increment à une clé étrangère. Il est très rare qu'une clé étrangère soit UNIQUE. Il est possible d'interdire les valeurs nulle avec NOT NULL

Les différentes contraintes de clés étrangères

Pour indiquer qu'une colone est une clé étrangère, il faut rajouter une contrainte de clés étrangères. Cela peut se faire lors de la création de la table ou en modifiant la table. Il vaut mieux commencer par créer toutes les tables et ajouter les contraintes de clé étrangères ensuite pour ne pas avoir à penser à l'ordre de création des tables.

Lors de l'ajout d'une contrainte de clé étrangère, il faut indiquer au serveur comment réagir lors de la suppression ou de la modification d'une valeur de la colonne clé primaire alors que cette valeur est utilisée dans la colonne cé étrangère. Pour bien comprendre cette phrase, un exemple est nécessaire.

Imaginons une table qui stocke des informations sur des commandes et des clients. Le schéma relationnel est le suivant :

Client (id_cli, nom_cli, adresse_cli)
Clé primaire : id_cli

Commande (id_commande, date_commande, id_cli)
Clé primaire : id_commande
Clé étrangère : id_cli en référence à id_cli de la table Client

 Ci dessous vous trouverez des extraits des tables Client et Commande

La colonne "id_cli" de la table commande est une clé étrangère qui permet d'indiquer quel est le client qui a passé une commande. On peut voir que la commande n°2 a été passé par le client n°1

Maintenant, imaginons qu'un utilisateur veuille supprimer le client n°2 ou changer l'id du client n°2 pour qu'il devienne le client n°27. Que doit faire le serveur avec la commande n°3 ? Si le serveur laisse faire l'utilisateur, alors la commande n°3 aura été passée par un client qui n'existe plus dans la base. Les données ne seront alors plus cohérentes.

Pour éviter cela, il est possible d'indiquer 3 type de comportements :

Interdire l'action de suppression ou de modification de la valeur dans la colonne clé primaire. Ce comprtement s'appelle RESTRICT et c'est le comportement par défaut d'une contrainte de clé étrangère. 

Répercuter l'action sur la valeur dans le colonne clé étrangère.Si un utilisateur supprime le client n°2, alors toutes les commandes passées par le client n°2 sont aussi supprimée. Ce comprtement s'appelle CASCADE

Définir comme NULL la valeur dans la colonne clé étrangère. Si un utilisateur supprime le client n°2, alors toutes les commandes passées par le client n°2 auront comme id_cli la valeur NULL. Ce comprtement s'appelle SET NULL. Evidemment, la colonne clé étrangère doit accepter les valeurs nulles.

Il faut comprendre que le comportement peut être différent pour la suppression ou la modification de la valeur de la clé primaire. Par exemple, on peut interdire avec RESTRICT la suppression de valeur dans la colonne clé primaire, mais utiliser CASCADE pour les modification.

La requête de modification d'une table pour ajouter une contrainte de clé étrangère

La requête qui permet de créer une clé étrangère est la suivante : 

ALTER TABLE nom de la table qui contient la clé étrangère ADD CONSTRAINT nom de la contrainte FOREIGN KEY(champ clé étrangère) REFERENCES table qui contient la clé primaire(champ clé primaire) [ON DELETE le comportement sur une suppression ON UPDATE le comportement sur une modification]

Pour notre exemple cela donne

##création des tables

CREATE TABLE Client(
id_cli SERIAL,
nom_cli VARCHAR(60),
adresse_cli TEXT,
PRIMARY KEY (id_cli)
);

CREATE TABLE Commande(
id_commande SERIAL,
date_commande DATE,
id_cli BIGINT(20) UNSIGNED,
PRIMARY KEY (id_commande)
);

##Modification de la table qui contient la clé étrangère pour ajouter une clé étrangère. Comme les comportements ne sont pas précisés, c'est les comportements RESTRICT qui sont appliqués sur les suppressions et modifications de valeurs de la colonne id_cli de la table Client. 

ALTER TABLE Commande
ADD CONSTRAINT fk_Commande_Client
FOREIGN KEY(id_cli) REFERENCES Client(id_cli);

## Si on avait voulu interdire les suppressions d'id_client de la table client mais répercuter les modification alors on aurait utilisé la requête suivante

ALTER TABLE Commande
ADD CONSTRAINT fk_Commande_Client
FOREIGN KEY(id_cli) REFERENCES Client(id_cli) ON DELETE RESTRICT ON UPDATE CASCADE;

Si on veut préciser un comportement 

Ajouter une contrainte de clé étrangère dans phpMyAdmin

Le debugage

Les messages d'erreurs lors de la création de contraintes ne sont pas toujours clairs. Voici ce qu'il faut vérifier :

Si le message indique "syntax error", il faut relire la requête pour trouver la faute d'orthographe dans les instructions SQL (mots en majuscule)

Si le serveur affiche un autre message d'erreur il faut vérifier que :

• Les noms des tables et champs sont bien orthographiés (majuscule compris)
• Vous faites le ALTER sur la table qui contient la clé étrangère et que le REFERENCES est fait sur la table qui contient la clé primaire. Souvent les étudiants inversent la table.
• La clé primaire et la clé étrangère sont bien du même type et ont les mêmes attributs. Normalement la clé primaire est en SERIAL c’est-à-dire BIGINT(20) UNSIGNED NOT NULL . Vérifiez que la clé étrangère est aussi BIGINT(20) UNSIGNED NOT NULL
• La clé primaire doit être défini comme clé primaire (une clé jaune à côté du nom du champ dans phpMyAdmin)

S'il y a des données dans la base, elles ne sont pas contraire à la contrainte de clé étrangère. Par exemple dans la colonne de la clé étrangère, il n'y a pas une valeur qui n'existe pas dans la colonne de la clé primaire à laquelle la clé étrangère fait référence.