Journal Gérer les clés étrangère avec Mysql

Posté par (page perso) .
Tags : aucun
1
18
juil.
2005
Depuis peut Mysql est capable de gérer les contraintes de clés étrangères[1]. Une fonction qui évite la gestion des correspondance par du code (php, c...).
Imaginons deux tables, familles et professions. Nous avons comme contraintes qu'une famille peut avoir plusieurs professions et qu'une professions peut avoir plusieurs famille, ce qui donne des cardinalitées 1,N de chaque côté. Pour ceux qui ont suivit les cours de développement pendant leurs BTS ;-) lorsque vous allez réaliser le MCD vous aurez deux entités (familles, professions) et une associations (prof_fam) qui va contenir les clés primaires des entités familles et professions. Voila pour la partie théorique.

Maintenant le code sql :

CREATE TABLE familles (
nom_fam VARCHAR(3) NOT NULL PRIMARY KEY,
designation VARCHAR(30) NULL,
code VARCHAR(3) NULL
) TYPE = InnoDB;

CREATE TABLE professions (
nom_prof VARCHAR(10) NOT NULL PRIMARY KEY
) TYPE = InnoDB;

CREATE TABLE prof_fam (
nom_fam VARCHAR(3) NOT NULL,
nom_prof VARCHAR(10) NOT NULL,
PRIMARY KEY(nom_fam,nom_prof),
FOREIGN KEY(nom_fam) REFERENCES familles(nom_fam) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY(nom_prof) REFERENCES professions(nom_prof) ON DELETE CASCADE ON UPDATE CASCADE,
INDEX(nom_fam),
INDEX(nom_prof)
) TYPE = InnoDB;


Attention l'ordre de création est important, d'abord les entités qui ne comporte pas de clés étrangères, ensuite les associations. Vous remarquez qu'il faut utiliser le format InnoDB[2]. Les lignes qui nous intéresses sont les FOREIGN KEY, c'est grâce à cette commande que nous indiquons à Mysql avec quelle entités les données présentes dans prof_fam sont liées. Pour plus d'informations sur la syntaxe aller faire un tour dans la documentation de mysql[3].

Le code PHP. Maintenant que nos tables sont créés nous pouvons les remplir, vous avez le choix, à la main ou alors grâce à des imports. J'ai choisi la deuxième option. Je ne vous donnerais pas le code complet, ce n'est pas le but de cette article mais juste la partie qui concerne l'erreur "a foreign key constraint fails" généré lorsque vous essayé de remplir en masse la table prof_fam. Il existe une commande SQL pour désactiver temporairement la gestion des clés étrangères : SET FOREIGN_KEY_CHECKS = 0

Le code PHP en question :

// Suppression temporaire de la gestion des clés étrangères
connecte();
$req = "SET FOREIGN_KEY_CHECKS = 0";
$result = mysql_query($req) or die('Erreur SQL !'.$sql.''.mysql_error());
foreach($columns_profam as $cle => $val)
{
$req = "INSERT INTO prof_fam(nom_fam, nom_prof) VALUES('$nom_fam', '$num_prof')";
$result = mysql_query($req) or die('Erreur SQL !'.$sql.''.mysql_error());
}

// activation de la gestion des clés étrangères.
$req = "SET FOREIGN_KEY_CHECKS = 1";
$result = mysql_query($req) or die('Erreur SQL !'.$sql.''.mysql_error());



[1] http://www.nexen.net/docs/mysql/annotee/innodb-foreign-key-constrai(...)
[2] http://www.innodb.com/index.php(...)
[3] http://www.nexen.net/docs/mysql/annotee/innodb.php(...)
  • # Quel journal !

    Posté par (page perso) . Évalué à 6.

    Je ne suis pas un connaisseur de MySQL, mais je l'utilise de plus en plus. Ca fait plaisir de lire un journal comme ça ! Un vrai, qui donne envie de lire les liens et d'en savoir plus !
    En un mot : Merci !

    Y?.
  • # Depuis peu ?

    Posté par (page perso) . Évalué à 3.

    Depuis peut Mysql est capable de gérer les contraintes de clés étrangères

    Ca fait un bail, comme indiqué sur le site de mysql (http://dev.mysql.com/doc/mysql/en/innodb-foreign-key-constraints.ht(...) ), c'est depuis la 3.23.44 qui date du 02/11/2001
    Mais le format de table par défaut était MyISAM à l'époque, et le support innoDB désactivé par défaut.
  • # ??

    Posté par (page perso) . Évalué à 3.

    C'est quoi l'interet d'utiliser les intégrités fonctionnelles si c'est pour les désactiver lors de l'insert ? :)

    En général, il est conseillé d'utiliser des int comme id plus que des strings et ainsi laisser le SGBD gérer les clés primaires (auto_increment avec Mysql ou sequence sous postgresql)
    • [^] # Re: ??

      Posté par (page perso) . Évalué à 2.

      L'intérêt ? Pouvoir faire des imports en masse. Par exemple pour mon cas on me donne trois fichiers, un qui contient les familles, un autre les professions et le dernier la correspondance entre les familles / professions. Pour importer je passe par un formulaire qui upload les fichiers sur le serveur, mon script les lit, retriture et envoi le tout dans mysql. Là où ca bloque c'est lorsque j'arrive à la table prof_fam, j'ai le fameux message a foreign key constraint fails. Donc pour réussir mon import je désactive les clés étrangères, j'importe, et je re-active mais j'utilise se sytème uniquement pour remplir en masse ma base, après pour le coups par coups je garde les clés étrangères sinon c'est clair que cela n'a aucun intérêt.

      Pour ce qui est des id, c'est clair c'est pas propre comme je l'ai fais, mais je vois pas trop comment je pourrais récupérer les id de la table familles (par exemple) qui correspondent au familles présentent dans mon fichier. A moins de faire un select...WHERE nom_fam='$mafamille' mais la c'est pas 10 sec qu'il faut au script, mais 1 heure. Mon fichier prof_fam contient 4000 lignes :(

      Born to Kill EndUser !

      • [^] # Re: ??

        Posté par . Évalué à 1.

        Pour ton import en masse, pourquoi ne pas importer d'abord les familles, puis les professions, et ensuite la table prof_fam ? Si les fichiers sont "propres", ça passe bien sinon il faut revoir les fichiers.

        En desactivant/réactivant tu risques d'avoir une base incohérente si tes données sont incohérentes...
        • [^] # Re: ??

          Posté par (page perso) . Évalué à 1.

          Justement si je fais comme tu le dis j'obtient le message "a foreign key constraint fails" et mes fichiers sont bien comme il le faut.

          mes fichiers sont composés comme ça :
          Famille :
          nom famille
          désignation
          code

          professions
          nom_prof

          prof_fam
          nom famille
          nom professions

          dans le dernier, par exemple pour la famille 0 il peut avec 5 lignes qui contient à chaque fois :
          0;TOTO
          0;TATA
          0;TUTU
          ...

          Et ainsi de suite pour les familles suivantes.

          Born to Kill EndUser !

          • [^] # Re: ??

            Posté par . Évalué à 2.

            Le message en question t'indique que soit "0" n'est pas présent comme clef dans Famille, soit que TOTO|TATA|TUTU n'est pas dans professions. Es-tu sûr des syntaxes (genre pb maj/min, espaces, etc...) ? Normalement ça devrait marcher en respectant les contraintes...
            • [^] # Re: ??

              Posté par (page perso) . Évalué à 2.

              Je remplis d'abord les tables familles et professions donc elles sont présentent. Après la syntaxe j'applique les filtres suivants :
              $fam = $val[0]; <-- $val est un valeur comprise dans le tableau qui correspond au fichier lu
              $fam = ltrim($fam);
              $fam = strtr($fam, CHAINE_A, CHAINE_B);
              $fam = str_replace(chr(10),'',$fam);
              $fam = str_replace(chr(13),'',$fam);

              Et tout ce pour chaque chaine intégré sur chaque fichier, donc il ne devrait pas avoir de soucis. Je mettrais bien le code complet mais ça fait un pavé de 300 lignes :(

              Born to Kill EndUser !

      • [^] # Re: ??

        Posté par (page perso) . Évalué à 2.

        Pour ce qui est des id, c'est clair c'est pas propre comme je l'ai fais, mais je vois pas trop comment je pourrais récupérer les id de la table familles (par exemple) qui correspondent au familles présentent dans mon fichier. A moins de faire un select...WHERE nom_fam='$mafamille' mais la c'est pas 10 sec qu'il faut au script, mais 1 heure.

        Ben tu indexes la colonnes nom_fam, et ton SELECT ne durera pas 1 heure.
      • [^] # Re: ??

        Posté par (page perso) . Évalué à 1.

        >A moins de faire un select...WHERE nom_fam='$mafamille' mais la >c'est pas 10 sec qu'il faut au script, mais 1 heure. Mon fichier prof_fam >contient 4000 lignes :(

        les non-utilisateur de mysql, doivent-ils comprendre que mysql ne sait pas indexer un champs de type chaine ???

        ou que tu debutes vraiment en bdd ?
        • [^] # Re: ??

          Posté par (page perso) . Évalué à 1.

          Mysql sait (il me semble) indexer les champs type chaine.
          Et en effet je débute... Enfin j'suis entre le débutant et l'expert, donc je ne connais pas toute les ficelles des bdd.

          Born to Kill EndUser !

      • [^] # Re: ??

        Posté par (page perso) . Évalué à 2.

        Si tu utilises InnoDB, tu peux utiliser des transactions :

        START TRANSACTION;
        ... // lignes qui cassent tout
        ... // lignes qui remettent tout en place
        COMMIT; // on envoie la sauce

        Comme ça s'il y a une erreur durant la mise à jour, tu gardes l'intégrité de ta table. Sinon, tu risques de perdre tes contraintes en route ... Il existe aussi ROLLBACK pour annuler la dernière transaction.

        Les transactions c'est vraiment super ! (là encore c'est un truc qui existe depuis InnoDB et depuis longtemps dans les normes SQL*)

        Haypo
      • [^] # Re: ??

        Posté par (page perso) . Évalué à 2.

        Nan, tu dois JAMAIS desactiver les verifications d'intégrité. Tu peux toujours faire des erreurs (meme lors des inserts). Si ça merde, ca vient de ton code qui insert pas les choses correctement (genre type pas correspondant etc)

        Pour le select where machin pour récuperer l'id pour l'insert dans la table "centrale", tu as un systeme efficace qu'ont tous les SGBD (ou déguisé), c'est le insert_id. Genre avec Mysql en php (et d'autres langages) tu fais appele a la fonction mysql_insert_id() qui te retourne l'id du dernier element inséré qui a la propriété AUTO_INCREMENT.
        • [^] # Re: ??

          Posté par (page perso) . Évalué à 2.

          le mysql_insert_id() je l'ai vu, mais faut que je re-organise tout mon code... :) mais je vais pas avoir le choix parce qu'avec le SELECT... Ca marche mais c'est pas super optimisé.

          Born to Kill EndUser !

  • # Gérer les clés étrangère avec Mysql

    Posté par . Évalué à 4.

    Au début, j'ai cru que c'était un journal qui aller poser une question/critique sur la gestion des clés étrangères dans MySQL. Du coup, je l'ai relu deux ou trois fois en espérant pouvoir répondre une fois la question trouvée. Mais je n'en ai trouvé aucune : il s'agissait bel et bien d'un simple partage de connaissance :)

    « Je vous présente les moines Shaolin : ils recherchent la Tranquillité de l'Esprit et la Paix de l'Âme à travers le Meurtre à Main Nue »

    • [^] # Re: Gérer les clés étrangère avec Mysql

      Posté par (page perso) . Évalué à 1.

      Bon ben la question c'est moi qui vais la poser :)

      Quid des performances ?

      J'ai eu lu que les FK n'étaient pas proposées par défaut par mysql (comprendre le type de table par défaut ne le support pas) pour des raisons de performances.

      Est-ce toujours vrai ?

      Deuxiement, est-ce toujours (aujourd'hui) pertinent ?
      -> implique plus de code pour gerer à la mano les FK.
      -> implique des erreurs d'intégrités.
      -> implique qu'une partie de l'information concernant la structure de donnée est absente de la définition de la structure.

      Bref, j'ai du mal à voir les avantages en fait :)
      • [^] # Re: Gérer les clés étrangère avec Mysql

        Posté par . Évalué à 1.

        La vérification des clefs étrangères impliquent un coût lors des écritures (insert/update/delete), mais dans une base bien conçue, tout cela se fait par des indexes et le coût est faible.

        Par contre, les avantages dépassent de loin la baisse de performance (généralement non visible si la machine est bien dimensionnée):

        - Implique moins de code, et non plus de code. Les programmes ont beaucoup moins de cas à traiter puisque les contraintes imposent une certaine forme aux données et il y a moins de cas d'erreurs à traiter.
        - Implique que personne ne peut mettre des anneries dans la base et perturber les programmes qui l'utilisent.
        - Implique que les informations sur la structure des données sont présentent dans la base, sans qu'il soit nécessaire d'écrire du code, et sans qu'il soit nécessaire de coder les contraintes dans chaque programme qui accède à la base.

        Si tu te poses la question des performances, tu devrais peut-être stoquer tes données dans des fichiers, il n'y a pas de contrainte d'intégrité et les performances sont meilleurs qu'avec un SGBD
        • [^] # Re: Gérer les clés étrangère avec Mysql

          Posté par . Évalué à 2.

          Si tu te poses la question des performances,...

          "Don't optimize, benchmark it !"

          Et il faut benchmarker tout le système, pas uniquement le SGBD : évidemment les perfs seront moins bonnes avec les contraintes, mais si tu passes 80% du temps dans des transferts réseaux ou des posts-traitements, il faut mieux optimiser de ce coté là que du coté SGBD.

          À chaque fois que j'ai supputé une optimisation - ou une dégradation des performances - en fonction de ce que je pensais, et ben j'ma gourré...
        • [^] # Re: Gérer les clés étrangère avec Mysql

          Posté par (page perso) . Évalué à 1.


          - Implique moins de code, et non plus de code. Les programmes ont beaucoup moins de cas à traiter puisque les contraintes imposent une certaine forme aux données et il y a moins de cas d'erreurs à traiter.
          - Implique que personne ne peut mettre des anneries dans la base et perturber les programmes qui l'utilisent.
          - Implique que les informations sur la structure des données sont présentent dans la base, sans qu'il soit nécessaire d'écrire du code, et sans qu'il soit nécessaire de coder les contraintes dans chaque programme qui accède à la base.


          Qu'on soit bien d'accord: je parlais du mode "Sans FK" hein !
          En ce qui concerne les bienfaits des FK tu preches un convaincu (comme pour la brandade de morue, quand c'est bien fait ©®).

          La question était:
          * étant donné que mysql n'a pas été concu à la base pour gerer les FK est-ce que leur utilisation n'est pas trop pénalisante aujourd'hui (par rapport à pgsql par exemple).

          Mais ta réponse me fait penser qu'a priori ce n'est pas le cas (et vive les FK du coup !)

Suivre le flux des commentaires

Note : les commentaires appartiennent à ceux qui les ont postés. Nous n'en sommes pas responsables.