Intéressé par des cours d'informatique en ligne ?
Visitez mon nouveau site
https://www.yesik.it !
Les contraintes d'intégrité référentielle permettent de garantir au niveau de la base que les liens entre les tables sont à tout moment valides.
| Objectifs | A la fin de ce tutoriel, vous saurez:
|
|---|---|
| Prérequis |
|
| Moyens |
Sommaire
Le problème
Dans ce tutoriel, nous allons nous appuyer sur la société fictive MatiBât.
MatiBât une société de vente de matériaux pour la construction et le bricolage (sable, gravier, ciment, etc.). Jusqu'à présent, l'entreprise fonctionne de manière "artisanale":
- Un client qui vient pour acheter des matériaux se présente directement au site de chargement.
- Sur place l'ouvrier charge le véhicule du client et lui remet un bon de chargement.
- Le client se rend ensuite à la caisse pour acquitter ses achats.
- La caissière échange le bon de chargement contre une facture.
- Cette facture sert de preuve de paiement et doit être présentée au vigile pour pouvoir sortir de l'enceinte de MatiBât.
La société désire développer une application Web/Intranet pour informatiser ces opérations. Les différentes opérations jusqu'alors effectuées sur papier seront effectuées sur des postes informatiques situées sur chaque point de chargement, à la caisse, et dans la guérite du vigile à la sortie.
La base de données retenue aura la structure suivante:
- Le champ MontantAcquité est le montant payé par le client à la caisse. Lorsque ce montant est égal au prix total des marchandises, le client a acquitté sa facture - et peut donc partir de MatiBât.
- Chaque ligne de la facture contient le prix unitaire des marchandises au moment de l'achat. Les éventuelles modifications du prix ne sont pas répercutées sur la facture.
- Les prix sont exprimés en centimes. En effet, il est déconseillé d'utiliser des nombres en virgule flottantes pour les prix afin d'éviter les erreurs d'arrondis.
- Les quantités sont entières et les matériaux vendus à la tonne: un client payera le même prix qu'il charge 1 tonne ou 750 kilos!
La base de données
Création de la base de données
CREATE DATABASE MatiBat CHARACTER SET "utf8"; USE MatiBat;
Note:
La clause CHARACTER SET "utf8" indique à MySQL que, sauf indication contraire, les caractères stockés dans la base seront encodés au format utf8.
Création des tables
CREATE TABLE Facture ( IdFacture int(6) NOT NULL AUTO_INCREMENT, NomClient varchar(255) NOT NULL, MontantAcquite int(8) NOT NULL, PRIMARY KEY (IdFacture) ); CREATE TABLE Ligne ( IdLigne int(6) NOT NULL AUTO_INCREMENT, Quantite int(6) NOT NULL, PrixUnitaire int(8) NOT NULL, IdArticle int(6) NOT NULL, IdFacture int(6) NOT NULL, PRIMARY KEY (IdLigne) ); CREATE TABLE Article ( IdArticle int(6) NOT NULL AUTO_INCREMENT, Designation varchar(255) NOT NULL, PrixUnitaire int(8) NOT NULL, PRIMARY KEY (IdArticle) );
Cette base est loin d'être optimale!
Il existe de nombreuses opportunités de refactorisation pour améliorer l'architecture de cette base. Néanmoins, elle est tout de même opérationnelle en l'état. Dans une démarche de développement agile les améliorations pourraient être réservées pour l'itération suivante.
Peupler la table
L'application peut maintenant peupler la base de données en envoyant des requêtes SQL. Si vous avez installé JBoss ou un serveur d'application compatible avec JBoss Seam, vous allez pouvoir facilement générer une application CRUD (Create/Read/Update/Delete) pour manipuler votre base de données.
L'installation de Seam est des plus simples puisqu'il suffit d'en télécharger une version compatible avec votre serveur d'application, puis de décompresser le dossier (dans votre dossier personnel, par exemple - pas dans $JBOSS_HOME!).
Il est ensuite possible de générer un projet à l'aide de la commande seam, comme ci-dessous (./seam setup pose pas mal de questions. Vérifiez bien vos réponses, sous peine d'avoir des soucis plus tard):
sh$ cd $SEAM_HOME sh$ ./seam setup sh$ ./seam create-project sh$ ./seam generate-entities sh$ cd $MY_SEAM_PROJECTS/matibat sh$ ant
Vous pouvez maintenant utiliser cette application pour saisir quelques articles, et une facture.
Note:
Si vous n'avez pas accès à JBoss Seam, vous pouvez simplement insérer les données à l'aide de requêtes SQL:
INSERT INTO `Article` VALUES (1,'Sable (la tonne)',1500), (2,'Gravier (la tonne)',1400), (3,'Gravier rouge (la tonne)',1550) ; INSERT INTO `Facture` VALUES (1,'Bob le Bricôleur',0); INSERT INTO `Ligne` VALUES (1,1,1500,1,1),(2,2,1550,3,1);
Convivialité, fonctionnement et robustesse
En l'état l'application n'est pas très conviviale. Des améliorations sont bien entendu possibles, mais ce n'est absolument pas le but de ce tutoriel. Ici, nous voulons voir si la base de données est fonctionnelle. Et c'est le cas. Par exemple, on peut connaître le détail d'une facture ainsi:
SELECT Designation, Quantite AS Qte, Ligne.PrixUnitaire AS PU, Quantite*Ligne.PrixUnitaire AS PTotal FROM Ligne,Article WHERE Ligne.IdArticle = Article.IdArticle AND Ligne.IdFacture=1 ;
Ou encore de connaitre le solde d'une facture:
SELECT NomClient, SUM(Quantite*Ligne.PrixUnitaire) AS MontantDu, MontantAcquite, SUM(Quantite*Ligne.PrixUnitaire)-MontantAcquite AS Reste FROM Facture,Ligne WHERE Ligne.IdFacture = Facture.IdFacture AND Ligne.IdFacture=1 GROUP BY Facture.IdFacture ;
Par contre, le problème de cette base est qu'elle n'est pas robuste. Ainsi il est parfaitement possible d'effectuer des opérations sur cette base qui la rendent invalide. Par exemple en supprimant une facture sans supprimer les lignes associés:
DELETE FROM Facture WHERE FactureId = 1 ;
ou encore en insérant une ligne qui référence un article inexistant ou supprimé:
INSERT INTO Ligne (Quantite, PrixUnitaire, IdArticle, IdFacture) VALUES (1,1500,99,1) -- Oups: pas d'article Id 99 ! ;
Le problème dans ces deux cas est que l'intégrité référentielle de la base est compromise. Bien entendu, il est possible d'argumenter sur le fait que c'est de la responsabilité de l'application de s'assurer que l'intégrité référentielle est maintenue, par exemple en supprimant les lignes d'une facture en même temps que celle-ci.
Néanmoins cette approche peut avoir des limitations: il est possible que l'application comporte un bug qui conduise à compromettre l'intégrité référentielle de la base. De plus, si la même base est utilisée par plusieurs applications, ils est impératif de coder les règles de maintien de l'intégrité référentielle dans chacune d'entre-elles.
Pour résoudre ce problème, il est possible de transférer la responsabilité du maintien des contraintes d'intégrité référentielle au SGBD-R lui-même.
Convertir les tables en InnoDB
Avec le format de table par défaut MyISAM, MySQL n'est pas capable de gérer les contraintes d'intégrité référentielle. Avant de continuer, il faut donc convertir nos tables au format InnoDB:
ALTER TABLE Facture ENGINE InnoDB; ALTER TABLE Ligne ENGINE InnoDB; ALTER TABLE Article ENGINE InnoDB;
Piège:
Si le moteur InnoDB ne peut pas démarrer, MySQL conserve silencieusement l'ancien format de table. Vous pouvez vérifier que la conversion a bien eu lieu à l'aide de la commande suivante:
SHOW CREATE TABLE Facture;
Si vous voyez apparaître ENGINE=InnoDB la conversion a bien eu lieu. Dans le cas contraire, il y a un problème avec InnoDB. Vous pouvez vérifier dans le fichier de log de MySQL. Plusieurs erreurs sont possibles, mais le plus couramment vous rencontrerez le problème de configuration suivant:
070929 18:34:29 mysqld started /usr/local/mysql/bin/mysqld: Can't create/write to file '/root/tmp/ib6hFdgB' (Errcode: 13) 070929 18:34:29 InnoDB: Error: unable to create temporary file; errno: 13
A cause d'un problème de permission, MySQL/InnoDB ne peut créer les fichiers temporaires nécessaires. Il suffit de modifier le fichier de configuration $MYSQL_HOME/my.cnf pour désigner un répertoire dans lequel le serveur pourra écrire:
[mysqld] tmpdir=/tmp
Ajouter les contraintes
Contrainte d'existence
Nous allons tout d'abord ajouter une contrainte pour dire que le champ IdArticle de la table Ligne doit référencer un IdArticle existant dans la table Article:
ALTER TABLE Ligne ADD FOREIGN KEY (IdArticle) REFERENCES Article(IdArticle) ;
Piège:
Si MySQL retourne une erreur 150 comme ci-dessous, cela signifie que la base contient des données qui ne respectent pas la contrainte que vous tentez d'imposer.
ERROR 1005 (HY000): Can't create table './MatiBat/#sql-2330_f.frm' (errno: 150)
Il vous faut corriger d'abord vos données avant de pouvoir imposer une contrainte d'intégrité référentielle.
Pour vérifier, vous pouvez tentez d'ajouter une nouvelle ligne à une facture existante faisant référence à un article inexistant:
INSERT INTO Ligne (Quantite, PrixUnitaire, IdArticle, IdFacture) VALUES (1,1500,99,1) -- Oups: pas d'article Id 99 ! ;
MySQL refuse d'insérer cette donnée puisqu'elle est en contradiction avec la contrainte que nous avons imposé:
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`MatiBat/Ligne`, CONSTRAINT `Ligne_ibfk_1` FOREIGN KEY (`IdArticle`) REFERENCES `Article` (`IdArticle`))
De la même manière MYSQL refuse les opérations suivantes sur la table Article:
UPDATE Article SET IdArticle = 8 WHERE IdArticle = 1 ; DELETE FROM Article WHERE IdArticle = 1 ;
Dans les deux cas la modification est refusée avec le même message:
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`MatiBat/Ligne`, CONSTRAINT `Ligne_ibfk_1` FOREIGN KEY (`IdArticle`) REFERENCES `Article` (`IdArticle`))
Suppression en cascade
Maintenant que nous savons qu'il n'est plus possible pour une ligne de référencer un article inexistant, nous pouvons nous attaquer à la relation Ligne-Facture. Ici également, il est important qu'il n'y ait aucune ligne qui fasse référence à une facture inexistante. Nous pourrions nous contenter d'adopter la même solution que précédemment. Mais ici, en plus, il y a une contrainte supplémentaire: il faut que lorsqu'une facture est supprimée, toutes les lignes associées soient supprimées également. C'est ce que l'on appelle une suppression en cascade. Cette contrainte s'ajoute ainsi à la table:
ALTER TABLE Ligne ADD FOREIGN KEY (IdFacture) REFERENCES Facture(IdFacture) ON DELETE CASCADE ;
La clause ON DELETE CASCADE signifie que lors de la suppression d'un enregistrement de la table Facture tous les enregistrements de la table Ligne y faisant référence seront supprimés également.
Piège:
C'est lorsqu'un enregistrement de la table Facture est supprimé qu'il y a suppression en cascade dans la table Ligne. Pas le contraire: la cascade ne fonctionne que dans un sens!
Vous pouvez tester en créant une facture puis quelques lignes. Lorsque vous supprimerez la facture, toutes les lignes associées seront supprimées.
Régénérer l'application
Faire apparaître explicitement les clés étrangères permet également de faciliter le travail des outils automatiques. Ainsi, JBoss Seam est capable d'utiliser ces informations pour améliorer la présentation des données et faciliter la saisie de celles-ci.
Vous pouvez re-générer et déployer l'application ainsi:
sh$ cd $SEAM_HOME sh$ ./seam generate-entities sh$ cd $MY_SEAM_PROJECTS/matibat sh$ ant
Remarque:
En explorant un peu l'application générée par Seam, vous remarquerez peut-être qu'il est possible de supprimer un article alors qu'il existe des lignes y faisant référence! En fait rien de magique là dedans: par défaut, Seam a généré du code pour supprimer automatiquement les enregistrements de la table Ligne en cas de suppressions d'articles. Comportement désirable ou pas? Cela dépend de l'application. Si puissants soient-ils, les outils automatiques laissent quand même un peu de travail au programmeur...
