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 = 0Le 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 Libre (site web personnel, Mastodon) . Évalué à 6.
En un mot : Merci !
Y?.
[^] # Re: Quel journal !
Posté par Philippe M (site web personnel) . Évalué à 5.
Born to Kill EndUser !
# Depuis peu ?
Posté par EppO (site web personnel) . Évalué à 3.
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.
[^] # Re: Depuis peu ?
Posté par Philippe M (site web personnel) . Évalué à 1.
Born to Kill EndUser !
# ??
Posté par Sylvestre Ledru (site web personnel) . Évalué à 3.
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 Philippe M (site web personnel) . É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. Mon fichier prof_fam contient 4000 lignes :(
Born to Kill EndUser !
[^] # Re: ??
Posté par jigso . Évalué à 1.
En desactivant/réactivant tu risques d'avoir une base incohérente si tes données sont incohérentes...
[^] # Re: ??
Posté par Philippe M (site web personnel) . Évalué à 1.
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 :
Et ainsi de suite pour les familles suivantes.
Born to Kill EndUser !
[^] # Re: ??
Posté par jigso . Évalué à 2.
[^] # Re: ??
Posté par Philippe M (site web personnel) . Évalué à 2.
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 chl (site web personnel) . Évalué à 2.
Ben tu indexes la colonnes nom_fam, et ton SELECT ne durera pas 1 heure.
[^] # Re: ??
Posté par PloufPlouf (site web personnel) . Évalué à 1.
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 Philippe M (site web personnel) . Évalué à 1.
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 Victor STINNER (site web personnel) . Évalué à 2.
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 Philippe M (site web personnel) . Évalué à 1.
http://www.nexen.net/docs/mysql/annotee/innodb-and-autocommit.php(...)
Born to Kill EndUser !
[^] # Re: ??
Posté par Sylvestre Ledru (site web personnel) . Évalué à 2.
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 Philippe M (site web personnel) . Évalué à 2.
Born to Kill EndUser !
# Commentaire supprimé
Posté par Anonyme . Évalué à 4.
Ce commentaire a été supprimé par l’équipe de modération.
[^] # Re: Gérer les clés étrangère avec Mysql
Posté par vieuxshell (site web personnel) . Évalué à 1.
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 Sébastien Koechlin . Évalué à 1.
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 jigso . Évalué à 2.
"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 vieuxshell (site web personnel) . Évalué à 1.
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 à celles et ceux qui les ont postés. Nous n’en sommes pas responsables.