Discussion :

[Jakartoine]

Bonjour à tous,

Après des centaines de recherche sur notre si bel ami Google depuis presque une semaine, je suis tout à fait perdu... Aucune résolution de mon problème !!!

Alors voilà, je suis en train de créer ma bdd pour la gestion des cotations d'une école, elle est complètement schématisée via DBMain. Grâce à cet outil, je peux en extraire un fichier .sql que je peux donc importer sur phpmyadmin !

Lors de l'importation, tout se passe bien jusqu'au moment où il arrive à mes "ALTER TABLE" concernant les contraintes des clés étrangères (leur ajout).
Un fameux code s'affiche :"#1215 - Cannot add foreign key constraint ", pourtant, tout est bien fait et j'ai beau retourner mon code sql dans tous les sens, pas moyen de trouver ce qui coince.... JE SUIS PERDU !!!

Voici le diagramme Entités-associations :


Voici le diagramme Relationnel ( celui sur lequel "Projet phph.sql" est généré) :

Et le .sql : Projet php.sql


J'espère que vous pourrez m'aider...

Merci d'avance,

Antoine RUCQUOY

[Sebwar]

Hello !

Le moteur de base n'est pas spécifié. Par défaut MySQL prend MyISAM hors les clé étrangère ne sont pas géré avec MyISAM, d’où l'erreur.
Pour mettre des clés étrangères tu peux utiliser InnoDB au lieu de MysISAM.

Par ailleurs, il serait bon d'utiliser des primary key int auto_increment plutôt que du varchar sinon par la suite tu risques d'avoir des problèmes

[escartefigue]

Par ailleurs, il serait bon d'utiliser des primary key int auto_increment plutôt que du varchar sinon par la suite tu risques d'avoir des problèmes

En effet, une clef primaire doit être stable, asémantique et concise, et là vous êtes loin du compte !

En particulier votre table Points_Bulletin dont la PK est composée de (id_eleve, nom_matiere, login_membre, nom_type_cours, nom_classe, nom_type_connaissance, annee_scolaire, nom_periode)
Soit pas moins de 178 caractères si je ne me suis pas trompé dans l'addition !
De plus, vous avez de très nombreuses colonnes annee définies en varchar, il ne faut jamais définir une année dans un format autre que date ou datetime ou timestamp, car c'est la cata ensuite pour les contrôles et les requetes !
Savez vous qu'il existe d'autres formats que le varchar et que le varchar présente certes quelques avantages, mais aussi des inconvénients ? (notamment lors des mises à jour)
Un mot de passe défini en varchar(150) pensez vous vraiment que ce soit possible de mémoriser un mot de passe aussi long ?

Vous devriez poster votre MCD dans le forum modélisation, car il y a matière à améliorations

[Jakartoine]

Coucou les gars, alors tout d'abord, je n'ai pas essayé vos conseils pour le moment ;-) Mais je ne vais pas y tarder ! (merci pour ces réponses plus que rapides !!!!!! )

Eh bien sachez que je suis tout à fait d'accord avec vous pour ces fameux id... C'est du casse-tête, lourd à lire et comprendre... Mais je pense ne pas avoir le choix !!!

Je m'explique, cette bdd fera l'objet d'une base de données utilisée par une école d'environ 950-1100 élèves, assez conséquente... j'en conclus.
Sachez qu'il y a environ, par période, 75000 entrées dans la table points_bulletin.. En faisant le compte, 75000*4 (périodes minimum) et nous sommes donc à 300.000 entrées par années.

Nous devons savoir également que nous gardons ces données sur 3 ans... (en prenant en compte qu'un même élève va peut-être réussir et changer de classe ou rater et rester dans une même classe où les profs changeront peut-être).
Manifestement, nous aurons environ 900.000 entrées dans points_bulletin. Un id auto_increment n'est donc, selon moi, pas le plus efficace car c'est seulement après trois ans que nous allons commencer à supprimer 300.000 lignes dans points_bulletins (environ). L'auto_increment, lui, ne peut être remis à zéro... sous peine d'une ambiguïté sur le champ unique "id" !!!

Une PK composée est donc, toujours selon moi, la plus simple des solutions (auriez-vous, sans doute, des meilleures solutions ?)


Revenons-en maintenant à l'élément date que vous me parliez. J'avais, aux premiers abords, mis un type date comme je l'avais si bien appris en cours de modélisation mais j'ai été surpris de voir que, en réalité, l'administrateur de l'application web pourra, à chaque nouvelle année, modifier l'année scolaire du genre [ '2015' - '2016' ] et où je me chargerai de concaténer le tout et de le mettre dans ce fameux VARCHAR(9) --> "2015-2016" (Beurk, je vous l'avouerai).

Dernière chose relevée, notre fameux mot de passe de 150 caractères... Eh bien, je n'ai aucune connaissance là-dedans.. Donc je me suis dit que j'allais le crypter en mde5 ou ssl mais je ne sais vraiment pas la longueur du mdp crypté.. d'où la justification du varchar(150) :-s


J'espère que vous me comprenez plus ou moins, j'essaie d'être assez clair (je n'ai jamais utilisé les forums de ce genre...) et je suis encore étudiant en informatique de gestion :-)

P.S. : Désolé pour les quelques fautes de pluriel.. Je m'y perds à cause du nom de mes entités du genre "table Années et son champ année" ^^

[Jakartoine]

Le moteur de base n'est pas spécifié. Par défaut MySQL prend MyISAM hors les clé étrangère ne sont pas géré avec MyISAM, d’où l'erreur.
Pour mettre des clés étrangères tu peux utiliser InnoDB au lieu de MysISAM.

J'ai essayé de mettre "ENGINE=InnoDB" pour chaque CREATE TABLE comme ceci :

create table Type_cours (
nom_type_cours varchar(30) not null,
nom_abbr varchar(5) not null,
constraint ID_Type_cours_ID primary key (nom_type_cours))ENGINE=InnoDB;

Mais en vain... :-(, je pensais que, lors d'un import sur phpMydAdmin, le moteur était configuré de base sur InnoDB :-/, je sèche un peu du coup :'( Un petit coup de main pour me mettre sur la bonne voie ? :-p

"SET default_storage_engine=InnoDB;" en début de fichier .sql ?

[Sebwar]

De manière générale il faut toujours mettre des PK auto_increment au tables ! je ne parle pas des table de jointure, qui elles seront formées avec les id des tables jointes

En mettant des chaines de caractères pour clé primaire, tu vas avoir de gros problème de performance et de maintenance !

et puis avec 300 000 entrées par an tu n'auras pas de problème avec le int auto_increment puisqu'il te faudra un peu plus de 7158 ans (le double en Unsigned) avant d'arriver au bout ça laisse de la marge avant de penser à le remettre a zero et puis si 7158 ans ce n'est pas assez, tu peux mettre un bigint auto_inscrement ce qui te laissera une marge de 30 milles milliards d'années (le double en unsigned)


Pour ton problème, il manque la reference :
ce n'est pas

Code :

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

1
2
3
alter table classes add constraint ref_class_membr_fk
     foreign key (login_membre)
     references membres;

mais ca :

Code :

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

1
2
3
alter table classes add constraint ref_class_membr_fk
     foreign key (login_membre)
     references membres(login_membre);

[Jakartoine]

Pour ton problème, il manque la reference :
ce n'est pas

Code :

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

1
2
3
alter table classes add constraint ref_class_membr_fk
     foreign key (login_membre)
     references membres;

mais ca :

Code :

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

1
2
3
alter table classes add constraint ref_class_membr_fk
     foreign key (login_membre)
     references membres(login_membre);

Je pense que les deux sont valides :p (du moins, j'ai vu en cours les différentes façons de faire.. mais ça reste à confirmer ! Je vais donc tester ça ;-) )

Sinon pour l'id, ça me rassure grandement du coup, je vais donc remodifier mon diagramme et repartir comme au début, sur des id

[escartefigue]

Eh bien sachez que je suis tout à fait d'accord avec vous pour ces fameux id... C'est du casse-tête, lourd à lire et comprendre...

On s'y fait assez vite, ne croyez pas que ce soit si compliqué

Je m'explique, cette bdd fera l'objet d'une base de données utilisée par une école d'environ 950-1100 élèves, assez conséquente... j'en conclus.
Sachez qu'il y a environ, par période, 75000 entrées dans la table points_bulletin.. En faisant le compte, 75000*4 (périodes minimum) et nous sommes donc à 300.000 entrées par années.

Ce qui fait des volumes très modestes pour une base de données

L'auto_increment, lui, ne peut être remis à zéro... sous peine d'une ambiguïté sur le champ unique "id" !!!

Sachant qu'une colonne de type integer peut prendre en peu plus de 2 milliard de valeurs distinctes vous avez le temps de voir venir
Au pire, vous pouvez utiliser du bigint, qui lui est limité à 2puissance 63 (!) valeurs distinctes , autant dire que votre base sera morte bien avant d'atteindre la limite

Une PK composée est donc, toujours selon moi, la plus simple des solutions (auriez-vous, sans doute, des meilleures solutions ?)

C'est surtout la plus mauvaise, car les perfs seront déplorables, et vous avez utilisé des colonnes sémantiques, ce qui rend vos valeurs instables
Par exemple, le nom est utilisé comme PK, or, suite à une erreur de saisie, vous corrigez "M. Dupond" en "M. Dupont", se faisant, vous avez des update en cascade à faire sur toutes vos tables via vos dépedanes de foreign key. Avec quelques centaines de milliers de lignes c'est déjà un effort conséquent et inutile, mais pour des tables de plusieurs centaines de millions, voire des milliards de lignes, c'est carément une application HS pendant des heures... ou des jours !
Ne dérogez jamais à la règle que j'ai mentionnée plus haut et que revoici : une clef primaire doit être stable, asémantique et concise

Revenons-en maintenant à l'élément date que vous me parliez. J'avais, aux premiers abords, mis un type date comme je l'avais si bien appris en cours de modélisation mais j'ai été surpris de voir que, en réalité, l'administrateur de l'application web pourra, à chaque nouvelle année, modifier l'année scolaire du genre [ '2015' - '2016' ] et où je me chargerai de concaténer le tout et de le mettre dans ce fameux VARCHAR(9) --> "2015-2016" (Beurk, je vous l'avouerai).

Surtout pas malheureux
D'une part, tout SGBD a la gentillesse de controler tout seul comme un grand les dates et heures, sous réserve que les colonnes soient du bon type, c'est toujours ça de moins à faire dans les programmes, et le SGBD ne se trompe jamais
D'autre part et surtout, comment voulez vous faire par exemple des tests de chevauchement de date (pour vérifier une règle de gestion) avec une colonne non date : c'est impossible
Bref, vous courrez à votre perte : les temps de traitement seront considérables pour vérifier les dates, et il y aura des erreurs

Dernière chose relevée, notre fameux mot de passe de 150 caractères... Eh bien, je n'ai aucune connaissance là-dedans.. Donc je me suis dit que j'allais le crypter en mde5 ou ssl mais je ne sais vraiment pas la longueur du mdp crypté.. d'où la justification du varchar(150) :-s

Si le MdP est une donnée saisie, interne à votre application, alors choisissez une taille maxi raisonnable, 10 car par exemple, et utilisez un format fixe char(10) plutôt qu'un varchar inutile pour des petites tailles et pénalisant dans ce cas.

J'espère que vous me comprenez plus ou moins, j'essaie d'être assez clair (je n'ai jamais utilisé les forums de ce genre...) et je suis encore étudiant en informatique de gestion :-)
P.S. : Désolé pour les quelques fautes de pluriel.. Je m'y perds à cause du nom de mes entités du genre "table Années et son champ année" ^^

Nous sommes tous là pour nous enrichir de l'expérience des autres

[Jakartoine]

Je vous remercie pleinement pour les aides que vous m'avez données, très précieuses !!!

J'ai pris en compte vos conseils et je les ai mis en application, j'ai réussi à créer ma base de données en ayant chipoter dans le .sql généré par DBMain. J'aurais dû m'en douter, un programme pareil n'est pas fiable à 100%, c'est quand même nous qui restons maîtres... Tout roule et j'espère ne plus avoir à revenir ici ;-)

Je vous souhaite, à tous les deux, une excellente soirée.


Informatiquement,

Antoine RUCQUOY

[Artemus24]

Salut Jakartoine.

Après des centaines de recherche sur notre si bel ami Google depuis presque une semaine, je suis tout à fait perdu...

Vous n'êtes pas si perdu que cela puisque vous avez trouvé le chemin vers "développez" afin d'obtenir une solution à votre problème.

Un fameux code s'affiche :"#1215 - Cannot add foreign key constraint "

Voici quelques conseilles (dans le désordre) :

1) la colonne servant de référence et la colonne servant de clef étrangère doivent être déclarées à l'identique.

2) on déclare et on remplie d'abord la table de référence avant celle contenant la clef étrangère.

3) Je n'aime pas utiliser les valeurs par défaut dans une déclarative. Voici ce que l'on doit déclarer pour utiliser une clef étrangère :

Code :

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

CONSTRAINT `ref_class_membr_fk` FOREIGN KEY (`login_membre`) REFERENCES `membres` (`login_membre`) ON DELETE RESTRICT ON UPDATE CASCADE

4) pourquoi faire un alter table afin d'ajouter cette clef étrangère dans la table ?
Vous pouvez le faire en introduisant directement la déclarative ci-dessus dans la table après les déclaratives de vos colonnes.

5) la colonne servant de référence pour la déclarative de votre clef étrangère doit être une clef primaire.
Si vous utilisez le type int, vous devez déclarer :

Code :

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

login_membre integer unsigned not null auto_increment primary key,

Avec int, le maximum sera de 2147483647. Inversement si vous mettez "int unsigned", le maximum sera de 4294967295.
Si vous avez besoin de beaucoup plus alors passez à "bigint unsigned".

6) a priori, la colonne login_member est de type varchar. Ce n'est pas la bonne façon de faire. Pourquoi ?
Une clef étrangère est juste un lien. Or vous utilisez non pas un lien ici, mais quelque chose qui est porteuse d'information. Et ça, c'est pas bien.
Si pour une raison quelconque, vous êtes tenu à modifier le contenu de la colonne "login_membre", vous devrez le faire partout dans votre base de données.
Autrement dit, lors de la création de ce lien, vous attribuez une valeur et cette valeur ne devra plus être changé par la suite.

Pour des raisons de performance, la clef primaire doit être une clef de type numérique, surtout si la volumétrie est conséquente.
Vous devez plutôt déclarer comme ci-après :

Code :

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

1
2
login_id integer unsigned not null auto_increment primary key,
login_membre varchar(255) not null

De ce fait, la colonne de référence de nom "login_membre" sera remplacé par la colonne "login_id" qui est la clef primaire !

7) vous devez utiliser le moteur InnoDB car sous MyIsam, les clef étrangères ne sont pas reconnues.
C'est principalement la raison de votre message d'erreur.

8) assurez-vous que vous avez la bon paramétrage suivant :

Code :

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

SET FOREIGN KEY CHECKS=1

9) Pour résoudre le problème de la période "2015-2016", vous devez vous poser une seule question : est-une pour faire des opérations de date dessus, ou est-ce juste informationnelle ?
Si vous faites des opérations de calculs sur vos dates, il est impératif de le mettre dans le bon format :

Code :

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

1
2
datdebper date not null,
datfinper date not null

Donc dans datdebper (date de début de période), vous mettez "2015". Et dans datfinper (date de fin de période), vous mettez "2016".

Inversement, si c'est juste une information qui ne sert pas à faire des calculs, oui vous pouvez mettre cela dans un char(9). Mais c'est rarement le cas.

Pour éviter les répétions de cette période dans vos tables, et aussi pour éviter de déclarer une date de début de période et une date de fin de période, le mieux est de transformer le tout en une clef primaire.
Le point d'entrée de votre base devra être une table de nom période scolaire, par exemple.
Vous indiquerez la date de début de la période scolaire et la fin de la période scolaire dans le type "date".
Comme dans cette table, vous utilisez une clef primaire, c'est cette clef primaire que vous devrez indiqué partout au lieu de cette référence à la période scolaire.

10) d'où l'importance d'avoir un MCD bien construit et qui réponde à toutes vos attentes.

@+