Discussion :

[Hervé Saladin]

Bonjour à tous,
je développe une BD sous MySQL 5.0.22 en InnoDB et ya deux trucs que je ne comprends pas :



Voici le contexte :

• J'ai une table 'thème' qui gère des thèmes
• Un thème à un code (son identifiant) et un nom
• Les thèmes sont organisés de façon arborescente, chaque thème peut donc être rattaché à un thème pere ou pas (si le theme est à la racine, le thème pere vaut null).

Voici la requete de création de ma table :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
CREATE TABLE `theme` (
  `id_theme` varchar(16) NOT NULL default '',
  `nom` varchar(128) NOT NULL default '',
  `theme_pere` varchar(16) default NULL,
  PRIMARY KEY  (`id_theme`),
  CONSTRAINT `FK_theme_1` FOREIGN KEY (`theme_pere`) REFERENCES `theme` (`id_theme`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

=> Vous remarquerez le "ON UPDATE CASCADE"
Avec ça, j'étais persuadé que lorsque je modifierais l'identifiant id_theme, ça mettrait à jour automatiquement tous les theme_pere de la table
Or, il n'en est rien



Voici mon 1er problème :
Quand je lance la requête suivante : (je précise qu'il existe dans ma table un theme ayant id_theme valant A et qui a des themes fils avec theme_pere valant A)

UPDATE theme SET id_theme='TEST' WHERE id_theme='A';

MySQL me répond :

#1451 - Cannot delete or update a parent row: a foreign key constraint fails (`bibnum/theme`, CONSTRAINT `FK_theme_1` FOREIGN KEY (`theme_pere`) REFERENCES `theme` (`id_theme`) ON DELETE CASCADE ON UPDATE CASCADE)

Et là je m'interroge puisque je croyais que le 'ON UPDATE CASCADE' était précisément le bon moyen d'éviter de violer la contrainte lors d'un update ... ???



Voici mon 2e problème :
Je me suis dit "qu'a cela ne tienne, je vais faire mes modifs avec des transactions", pensant que je pouvais modifier mes id comme voulais du moment que l'etat des donnée respecterait la contrainte au moment ou je balance mon "commit"
J'ai donc essayé la transaction suivante :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
SET AUTOCOMMIT=0;
START TRANSACTION;
UPDATE theme SET theme_pere='TEST' WHERE theme_pere='A';
UPDATE theme SET id_theme='TEST' WHERE id_theme='A';
COMMIT;

et là, MySQL me répond toujours :

#1452 - Cannot add or update a child row: a foreign key constraint fails (`bibnum/theme`, CONSTRAINT `FK_theme_1` FOREIGN KEY (`theme_pere`) REFERENCES `theme` (`id_theme`) ON DELETE CASCADE ON UPDATE CASCADE)

D'où mes questions :

• Pourquoi le 'ON UPDATE CASCADE' ne joue pas son rôle ?
• Pourquoi ma transaction ne passe pas ?
• Y'a-t-il un réglage spécial à faire sur la conf de MySQL ?
• Pourquoi je comprends paaas ?

Si l'un d'entre vous avait la réponse, ne serait-ce qu'a une de ces questions, je lui en serait reconaissant à vie !
Merci d'avance

[qi130]

Peut-être un début d'explication :

Une entorse aux standards : si ON UPDATE CASCADE ou ON UPDATE SET NULL cascade récursivement jusqu'à la même table, elle agira comme pour un RESTRICT. Cela est fait pour éviter les boucles infinies des modifications en cascade

http://dev.mysql.com/doc/refman/5.0/...nstraints.html

[mathieu]

le problème est que tu essayes de modifier une clé primaire
si tu as un code pour ton théme tu auras deux champs pour retrouver le théme : le champ primaire "id_theme" du type "INT autoincrement" que tu ne touche plus et ensuite un champ "code_theme" du type "varchar"
et donc pour le lien entre père et fils le champs "theme_pere" sera du même type que la clé primaire

est ce que tu vois comment tourner ton problème ?

[Hervé Saladin]

OK, tout d'abord merci pour vos réponses

Peut-être un début d'explication :
Citation:
Une entorse aux standards : si ON UPDATE CASCADE ou ON UPDATE SET NULL cascade récursivement jusqu'à la même table, elle agira comme pour un RESTRICT. Cela est fait pour éviter les boucles infinies des modifications en cascade
http://dev.mysql.com/doc/refman/5.0/...nstraints.html

Aaaaaaaah ! ouais, d'accord je comprends mieux !
C'est le genre de "petit détail" utile à connaître mais qu'on connaîtra jamais tant qu'on se sera pas cassé les dents dessus ....

le problème est que tu essayes de modifier une clé primaire
si tu as un code pour ton théme tu auras deux champs pour retrouver le théme : le champ primaire "id_theme" du type "INT autoincrement" que tu ne touche plus et ensuite un champ "code_theme" du type "varchar"
et donc pour le lien entre père et fils le champs "theme_pere" sera du même type que la clé primaire

est ce que tu vois comment tourner ton problème ?

Oui, je vois ce que tu veux dire, cette solution n'est pas la plus "belle" d'un point de vue conception, mais je crois que je n'ai pas le choix, donc c'est ce que je vais faire.
Ce qui m'ennuie le plus c'est que du coup, je vais devoir retoucher mes classes métier en PHP ...
Y font c**** chez mysql de faire des "entorses aux standarts", non mais ho !

[qi130]

Tu peux peut-être t'en sortir en "externalisant" la colonne père dans une table de relation qui ne contiendra que les PK (père & enfant).
L'avantage étant qu'un père pourra avoir plusieurs enfants.

[Hervé Saladin]

Bon, ben pour ceux que ça interesserait, voici comment je m'en suis sorti :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
CREATE TABLE `theme` (
  `id_theme` int(10) unsigned NOT NULL auto_increment,
  `code_theme` varchar(16) NOT NULL default '',
  `nom` varchar(128) NOT NULL default '',
  `id_theme_pere` int(10) unsigned default NULL,
  PRIMARY KEY  (`id_theme`),
  UNIQUE KEY `code_theme` (`code_theme`),
  KEY `FK_theme_1` (`id_theme_pere`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=148 ;

évidamment, j'ai du modifier mon code PHP ...

Tu peux peut-être t'en sortir en "externalisant" la colonne père dans une table de relation qui ne contiendra que les PK (père & enfant).
L'avantage étant qu'un père pourra avoir plusieurs enfants.

Bah finalement j'ai pas fait comme ça, pasque de toutes façons ça me faisait retoucher mon code, et sinon pour le pere qui peut avoir plusieurs enfants, c'est deja le cas avec ma structure : comme c'est le theme_pere la clé étrangère, plusieurs themes peuvent avoir le meme theme pere (alors que si j'avais fait un champ theme_fils, ça n'aurait pas été vrai)

En tout cas merci à vous deux pour votre aide, je n'aurais jamais compris tout seul le coup du "on update cascade" qui agit comme un "restrict"
Merci

[mathieu]

Oui, je vois ce que tu veux dire, cette solution n'est pas la plus "belle" d'un point de vue conception, mais je crois que je n'ai pas le choix, donc c'est ce que je vais faire.

je viens de relire cette phrase et je me demandais si tu parlais de ma solution.
parce qu'un table devrait tojours avoir une clée primaire, donc du point de vue de la conception, ma proposition est plus "belle", non ?

[Hervé Saladin]

Ben dans ma solution aussi y'avait une clé primaire : le code.
Dans mes règles de gestion, chaque thème à un code unique qui l'identifie, donc c'est une clé primaire.
Là, j'ai rajouté un id entier qui ne contient aucune information utile à l'appli et qui se substitue au code pour la clé primaire. J'ai donc deux clés primaires dont l'une (l'id) ne sert qu'a faire "marcher" la solution au niveau de la BD, rendant du coup l'autre (le code) inutile du point de vue de la BD mais je dois quand même la garder et empêcher les doublons (d'où la contrainte "UNIQUE") à cause des besoins de l'appli.
Donc, si ça avait marché avec le code comme clé primaire, ça aurait été plus "beau" car j'aurais eu un champ en moins et en plus ça correspondait avec la logique de l'appli (c.a.d.le theme est identifié par son code).
Mais bon, je chipote là, ce n'est qu'un détail.
Au final ta solution (que j'ai appliquée) à au moins le mérite de marcher, contrairement à la mienne ...
Mille mercis, donc

[mathieu]

par définition, la clé primaire sert uniquement à identifier un enregistrement dans la table de manière unique et en doit pas être utilisée comme information dans l'application
en mettant un champ entier autoincrementé, la clé se crée automatiquement, tu n'as pas à gérer une clé existante ou le calcul de la clée et donc en faisant ça pour tes prochains développement tu verras que tout se mets en place tout seul et çà te ferra une chose en moins à gérer