Accueil > Articles > Innodb et les clés étrangères

Innodb et les clés étrangères  

Publié par Sebastien Dudek il y a 6 mois

Un des reproches souvent fait à MYSQL, c'est sa non gestion des clés étrangères. Cependant ce n'est pas tout à fait vrai, car le moteur InnoDB des tables MYSQL permet de gérer de telles clées avec des fonctions bien utiles. Regardons un petit exemple.

MySQL est une base de données qui utilise différents moteurs pour fonctionner. En fonction de vos besoins vous pouvez assigner MyIsam, InnoDB ou HEAP comme moteur de vos tables. La granularité fait que, contrairement à la majorité des SGBD, vous pouvez définir un moteur au niveau d'une table (et non de l'ensemble des tables)

Chacun de ces moteurs à ses avantages et ses inconvénients.

  • MyISAM : rapide mais non transactionnel
  • InnoDB : Plus de fonctionnalités mais moins rapide.
  • HEAP : très rapide (en mémoire) mais volatile.

InnoDb c'est quoi ?

InnoDB fournit à MySQL un gestionnaire de table transactionnelle, avec validation (commits), annulations (rollback) et capacités de restauration après crash.

InnoDB utilise un verrouillage de lignes, et fournit des lectures cohérentes comme Oracle, sans verrous. Ces fonctionnalités accroissent les possibilités d'utilisation simultanées des tables, et les performances. Les tables InnoDB sont les premières tables MySQL qui supportent les contraintes de clés étrangères ( FOREIGN KEY ).

InnoDB a été conçu pour maximiser les performances lors du traitement de grandes quantités de données.

Description issue de la documentation officielle mysql française :

Documentation française sur Nexen.

Petit exemple simple

Comme c'est expliqué ci-dessus, InnoDb permet de gérer les clés étrangère ce qui est quand même très très pratique.

Voyons un exemple simple, créez un base de données, et appelez là comme vous le souhaitez. Ensuite créez les tables suivantes:

  1. CREATE TABLE auteurs (
  2.   id_auteur tinyint(2) NOT NULL AUTO_INCREMENT,
  3.   nom_auteur varchar(20) NOT NULL DEFAULT '',
  4.   prenom_auteur varchar(20) NOT NULL DEFAULT '',
  5.   PRIMARY KEY  (id_auteur)
  6. ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
  7.  
  8. CREATE TABLE livres(
  9. id_livre tinyint( 2 ) NOT NULL AUTO_INCREMENT ,
  10. titre_livre varchar( 40 ) NOT NULL DEFAULT '',
  11. id_auteur tinyint( 2 ) NOT NULL,
  12. PRIMARY KEY ( id_livre ) ,
  13. FOREIGN KEY (id_auteur) REFERENCES auteurs(id_auteur) ON DELETE CASCADE
  14. ) ENGINE = InnoDB DEFAULT CHARSET = latin1 AUTO_INCREMENT =1 ;

  

 Et ensuite, ajoutez le contenu pour ces deux tables:

  1. INSERT INTO `auteurs` VALUES (1, 'Werber', 'Bernard');
  2. INSERT INTO `auteurs` VALUES (2, 'Singh', 'Simon');
  3.  
  4. INSERT INTO `livres` VALUES (1, 'Les Fourmis', 1);
  5. INSERT INTO `livres` VALUES (2, 'Les Thanatonautes', 1);
  6. INSERT INTO `livres` VALUES (3, 'L''Ultime secret', 1);
  7. INSERT INTO `livres` VALUES (4, 'Le Père de nos pères', 1);
  8. INSERT INTO `livres` VALUES (5, 'Histoire des codes secrets', 2);
  9. INSERT INTO `livres` VALUES (6, 'Le Dernier Théorème de Fermat', 2);

 

Maintenant, admettons que nous souhaitons supprimer l'auteur Bernard Werberd de notre base de données. Si on utilisait le type de table MyIsam (celui par défaut utilisé dans MYSQL) il faudrait faire par exemple:

  1. DELETE
  2. FROM livres
  3. WHERE livre.id_auteur = 2

 

puis

  1. DELETE
  2. FROM auteurs
  3. WHERE auteurs.id_auteur = 2

 

Ou plus rapidement:

  1. DELETE livres.*, auteurs.*
  2. FROM livres, auteurs
  3. WHERE livres.id_auteur  = auteurs.id_auteur
  4. AND auteurs.id_auteur = 2

 

Maintenant essayez la requête suivante:

  1. DELETE
  2. FROM auteurs
  3. WHERE id_auteur = 1

 

Et regardez les résultats, non seulement l'auteur désigné est supprimé mais aussi les livres qui lui sont associés. Tout ça grâce au moteur innodb et à son support des clés étrangères. D'ailleurs il est bien mieux, pour l'intégrité de la base d'utiliser un système de clés étrangère gérées par le SGDB car sinon c'est le créateur de l'application qui doit penser à tout gérer, et on est jamais à l'abri d'une erreur ou d'un oubli. Mais attention, l'inverse ne fonctionnera pas, c'est à dire que:

  1. DELETE
  2. FROM livres
  3. WHERE id_auteur = 2

 

Supprimera toutes les entrées correspondantes mais uniquement dans la table livres. Pourquoi ? Parce que la clé étrangère est importée de la table auteur, si vous supprimez un auteur tout ce qui y est rattaché va être supprimé, par contre vous pouvez avoir envie de supprimer juste un livre de cet auteur et pas forcément de tout vider (même si, dans ce cas précis vous enlevez tous les livres, vous avez peut-être envie de conserver l'auteur ;) ). Faites donc bien attention à ça lors de la modélisation votre base de données ;)

C'est quand même pratique non ? En maintenant vous ne pourrez plus dire que MYSQL ne gère pas les clés étrangères ;)

Cet article a été fait avec l'aimable autorisation de Jérôme Renard aka qwix et peut être consulté à l'adresse suivante: :

Article original

Retour à l'accueil des articles