Intégrité référentielle et utilisation de fonctions SQL, ou comment rendre au SGBD une partie de ses responsabilités.

Darko Stankovski — Thu, 12 Mar 2009 18:03:00 +0100

Faisons suite au dernier billet de Céline en restant dans le domaine des bases de données et des petites astuces connues de tous mais jamais utilisées. Intéressons nous à la gestion de l'intégrité référentielle et au calcul d'identifiants en laissant la base faire ce qu'elle sait faire et éviter un développement applicatif inutile.

Contexte

Posons l'exemple. Soit donc une base avec les tables suivantes :

Categorie (id, libelle)
SousCategorie (idCategorie, id, libelle)
Employe (id, idSousCategorie, nom).

Ah ces chers employés... Nous allons donc catégoriser les employés en catégorie et sous-catégorie ou pas. Une sous catégorie est donc automatiquement associée à une catégorie et un employé peut ou non être associé à une sous-catégorie. La clef primaire de chaque table est évidente. Mais on va compliquer un peu et définir que pour les sous-catégories, l'Id est relatif à la catégorie et n'est donc pas unique. Par contre, le couple est unique. Les champs Id seront donc les clefs primaires pour les tables Categorie et Employe et la table SousCategorie aura une clef composite idCategorie, id.

Évidemment, il faut ensuite poser les clefs étrangères. Sur ce modèle "simple", elles sont claires : idSousCategorie d'Employe référence id de SousCategorie et idCategorie de SousCategorie référence id de Categorie. Allons-y pour la création classique des tables sous MySql.

CREATE TABLE `categorie` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `libelle`VARCHAR(30) NOT NULL,
    PRIMARY KEY(id))
) ENGIN=InnoDB;
 
CREATE TABLE `souscategorie` (
    `idcategorie` INT NOT NULL,
    `id` INT NOT NULL,
    `libelle`VARCHAR(30) NOT NULL,
    PRIMARY KEY(idcategorie, id),
    CONSTRAINT `FK_SOUSCATEGORIE` FOREIGN KEY (idcategorie)
        REFERENCES `categorie` (`id`))
) ENGIN=InnoDB;
 
CREATE TABLE `employe` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `idsouscategorie`INT DEFAULT NULL,
    `nom`VARCHAR(40) NOT NULL,
    PRIMARY KEY(id),
    CONSTRAINT `FK_EMPLOYE` FOREIGN KEY (idsouscategorie)
        REFERENCES `souscategorie` (`id`))
) ENGIN=InnoDB;

Voilà. Incroyable et révolutionnaire. Bon, maintenant, regardons un peu la logique de traitement. On peut ajouter des catégories. On peut ajouter des sous-catégories à une catégorie existante. On peut créer un employé, et on peut affecter un employé à une sous-catégorie. Dans le même ordre d'idée, on peut supprimer un employé, une sous-catégorie si aucun employé ne lui est affecté, et une catégorie si elle ne possède pas de sous catégorie.

Lors de la gestion des projets mettant en jeu une base qui ressemblerait à ça et l'applicatif qui va avec, on voit souvent la conception de la base s'arrêter là et les traitements remonter au niveau de l'applicatif. Ainsi, la fonctionnalité de suppression d'une catégorie supprimera toutes les sous-catégories avant de supprimer la catégorie. Fonctionnellement c'est correct, mais que d'énergie dépensée pour pas grand chose. Surtout si les règles sont que pour toute suppression de catégorie, les sous-catégories doivent être simplement supprimées et pour toute suppression de sous-catégorie, les employés doivent simplement en être désaffectés.

Gestion de l'intégrité référentielle

Les bases de données sont capables de gérer leur intégrité référentielle. Il suffit pour ça de rajouter une clause ON DELETE aux contraintes de clef étrangère.

Cas de la suppression d'une sous-catégorie

Une sous-catégorie ne peut être supprimée que si aucun employé ne lui est affecté (contrainte FK_EMPLOYE). Supprimer une sous-catégorie ne doit entraîner que la désaffectation de l'employé de ladite catégorie. Il suffit alors d'utiliser la clause ON DELETE SET NULL. Ainsi lors de la suppression d'une sous-catégorie, tous les employés affectés à cette sous-catégorie verront leur champ idsouscategorie modifié en NULL. Le code pour créer cette table sera maintenant :

CREATE TABLE `employe` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `idsouscategorie`INT DEFAULT NULL,
    `nom`VARCHAR(40) NOT NULL,
    PRIMARY KEY(id),
    CONSTRAINT `FK_EMPLOYE` FOREIGN KEY (idsouscategorie)
        REFERENCES `souscategorie` (`id`)
        ON DELETE SET NULL)
) ENGIN=InnoDB;

Cas de la suppression d'une catégorie

Une catégorie ne peut être supprimée que si aucune sous-catégorie ne lui est affectée (contrainte FK_SOUSCATEGORIE). Contrairement à l'employé, une sous-catégorie n'a aucun sens à elle toute seule. Ce coup-ci, il faudra utiliser la clause ON DELETE CASCADE. Ainsi, pour la suppression d'une catégorie, la base supprimera toutes les sous-catégories s'y référant. Le code pour créer cette table sera maintenant :

CREATE TABLE `souscategorie` (
    `idcategorie` INT NOT NULL,
    `id` INT NOT NULL,
    `libelle`VARCHAR(30) NOT NULL,
    PRIMARY KEY(idcategorie, id),
    CONSTRAINT `FK_SOUSCATEGORIE` FOREIGN KEY (idcategorie)
        REFERENCES `categorie` (`id`)
        ON DELETE CASCADE)
) ENGIN=InnoDB;

Ainsi, l'intégrité référentielle des données est toujours respectée quel que soit le contexte et est indépendante d'un applicatif. Supprimer une catégorie supprimera les sous-catégories affectées et la suppression d'une sous-catégorie désaffectera les employés affectés. En conséquence, le développement de l'applicatif n'est que facilité. Chaque entité a des responsabilités dédiées et limitées, avec comme conséquence la plus importante, le fait que quelle que soit l'action sur la base, l'intégrité référentielle est toujours respectée.

Ajout de sous-catégories

Revenons à nos sous-catégories. En ce qui les concerne, la clef primaire est une clef composite. Il est impossible d'utiliser des instructions comme AUTO_INCREMENT car l'id de la sous-catégorie doit être relatif à la catégorie (catégorie 1, sous catégorie 1 ; catégorie 1, sous catégorie 2 ; catégorie 1, sous catégorie 3 ; catégorie 2, sous catégorie 1 ; catégorie 2, sous catégorie 2). Ne demandez pas pourquoi, c'est comme ça. Il faudra donc affecter les données à la main. Il est évident qu'il faudra récupérer l'id de la catégorie avant de faire une insertion. Quand à l'affectation de l'identifiant de la sous-catégorie, on peut utiliser la requête (écrite sous la forme d'une requête paramétrée Java) :

SELECT MAX(id) FROM souscategorie WHERE idcategorie = ?

ce qui fait que nous pouvons proposer la requête suivante pour une insertion :

INSERT INTO `souscategorie` (`idcategorie`, `id`, `libelle`) VALUES
(?, (SELECT MAX(id) FROM souscategorie WHERE idcategorie = ?) + 1, ?);

Sauf qu'il y a des cas où cette requête ne marche pas... S'il s'agit d'ajouter une sous-catégorie à une catégorie qui n'a pas encore de sous-catégorie, le SELECT rend un beau NULL. Aie...

Alors évidemment, on peut laisser l'applicatif gérer le select et agir en fonction, mais cela alourdit le code applicatif et provoque deux appels à la base. On peut aussi utiliser certaines fonctions SQL propres à la base pour réaliser cette action. Ici, nous utiliserons la fonction IFNULL. La signature de cette fonction est IFNULL(expr1, expr2). Si expr1 n'est pas null, IFNULL renvoi expr1, sinon elle renvoie expr2. Et bien voila notre solution :

INSERT INTO `souscategorie` (`idcategorie`, `id`, `libelle`) VALUES
(?, (IFNULL((SELECT MAX(id) FROM souscategorie WHERE idcategorie = ?), 0)) + 1, ?);

Dans ce cas, s'il n'y a pas encore de sous-catégorie pour la catégorie voulue (le select renvoi null), la fonction IFNULL renverra 0 et nous aurons notre premier identifiant qui vaudra 1.

A noter que IFNULL est spécifique à MySQL. Sous Oracle ou Informix, il s'agit de la fonction NVL.

En conclusion

Il n'y a aucune haute voltige dans ce qui est présenté ici. Malheureusement, ce sont des situations souvent observées dans beaucoup de projets de gestion. Dans ce domaine, tout projet fait intervenir un applicatif et une base de données. Mais on observe bien souvent que le développement de la base de données se limite à définir les tables, leurs clefs primaires, leurs clefs étrangères et leurs indexs. Dans ces contextes, les contraintes d'intégrité référentielle sont gérées au niveau applicatif. La conséquence est évidente aussi bien en terme d'intégrité des données qui ne peut être assurée en fonction du type d'intervention, mais potentiellement en terme de coût de développement. Les SGBD sont aujourd'hui assez riches pour s'occuper aussi bien du stockage des données que d'assurer leur qualité. Lors du design de vos bases, n'hésitez pas à explorer les possibilités du SGBD.