Problème avec la création de mes tables innoDB (Pb de Foreign Key)

Version imprimable

Bonsoir,

Pour mon application (développé avec RoR) je compte utiliser une base de données MySQL (une base assez simple pour l'instant en InnoDB avec des clés étrangères).

Ci-dessous voici le contenu de mon fichier pour la création de mes tables:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
drop table if exists articles;
create table articles(
id int(11) not null auto_increment,
titre varchar(30) not null,
sous_titre varchar(60),
contenu text not null,
membre_id int(11),
source_url varchar(255),
categorie_id int(5),
date_post timestamp not null,
date_edit timestamp not null,
publication int(5) not null default 0,
PRIMARY KEY(id, membre_id, categorie_id),
FOREIGN KEY(membre_id) REFERENCES membres(id) ON UPDATE CASCADE ON DELETE SET NULL,
FOREIGN KEY(categorie_id) REFERENCES categories(id) ON UPDATE CASCADE ON DELETE SET NULL)
ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
drop table if exists categories;
create table categories(
id int(5) not null auto_increment,
nom varchar(20) not null,
description varchar(100),
logo_url varchar(255),
PRIMARY KEY(id))
ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
drop table if exists membres;
create table membres(
id int(11) not null auto_increment,
login varchar(20) not null,
pass varchar(255) not null,
actif tinyint not null default 0,
mail varchar(255) not null,
signature varchar(100),
date_first timestamp not null,
date_last timestamp not null,
avatar_url varchar(255),
grade_id int(11) not null default 0,
anciengrade_id int(11) not null default 0,
PRIMARY KEY(id, grade_id, anciengrade_id),
FOREIGN KEY(grade_id) REFERENCES grades(id),
FOREIGN KEY(anciengrade_id) REFERENCES grades(id))
ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
drop table if exists grades;
create table grades(
id int(11) not null auto_increment,
nom varchar(20) not null,
droit int(11) default 0,
PRIMARY KEY(id))
ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
drop table if exists commentaires;
create table commentaires(
id int(11) not null auto_increment,
membre_id int(11),
date_post timestamp not null,
contenu text not null,
article_id int(11),
PRIMARY KEY(id, membre_id, article_id),
FOREIGN KEY(membre_id) REFERENCES membres(id) ON UPDATE CASCADE ON DELETE SET NULL,
FOREIGN KEY(article_id) REFERENCES articles(id) ON UPDATE CASCADE ON DELETE SET NULL)
ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
drop table if exists images;
create table images(
id int(11) not null auto_increment,
nom varchar(20) not null,
image_url varchar(255) not null,
width smallint(5) not null,
height smallint(5) not null,
extension varchar(5) not null,
membre_id int(11),
PRIMARY KEY(id, membre_id),
FOREIGN KEY(membre_id) REFERENCES membres(id) ON UPDATE CASCADE ON DELETE SET NULL)
ENGINE=InnoDB DEFAULT CHARSET=latin1;

En executant le code de mon fichier, une erreur MySQL me revient:

Code:

ERROR 1005 (HY000) at line 2: Can't create table './nom_de_ma_base/articles.frm' (errno: 150)

La base de données est vide, nouvellement créé, errno: 150 indique une erreur sur les clé étrangères, mais ayant parcourue pas mal de post concernant cette erreur je ne parvient toujours pas à résoudre la mienne (j'ai posté un sujet identique sur le site de RoR).

Version mysql: MySQL 5.0.32-Debian_3-log :bug:

Merci d'avance pour votre aide.

06/03/2007, 13h53
Mamilie

Les types de tes champs à relier ne sont pas identiques. Essaye de les changer. Ca devrait marcher...

Je ne peux pas essayer ici, désolée...
06/03/2007, 13h53
Cybher

salut,

il faut que tes tables catégories et membre soit créées avant la table article. en effet pour faire référence à une colonne d'une autre table, il faut que celle ci existe

Merci pour vos deux réponses, un changement de l'ordre de création m'a permis d'avancer un minimum, a priori par contre tous mes types de variables référencées sont bien identique (sauf erreur de ma part).

J'avance dans le soucie, cela viendrait de mes clauses ON UPDATE et ON DELETE des clés étrangères. :yaisse2: :yaisse2:

Bon j'investigue et je poste l'avancée.

Pour les intéréssés voici le nouveau fichier de création:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
DROP TABLE IF EXISTS categories;
CREATE TABLE categories(
id int(11) NOT NULL AUTO_INCREMENT,
nom varchar(20) NOT NULL,
description varchar(100),
logo_url varchar(255),
PRIMARY KEY(id))
ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
DROP TABLE IF EXISTS grades;
CREATE TABLE grades(
id int(11) NOT NULL AUTO_INCREMENT,
nom varchar(20) NOT NULL,
droit int(11) DEFAULT 0,
PRIMARY KEY(id))
ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
DROP TABLE IF EXISTS membres;
CREATE TABLE membres(
id int(11) NOT NULL AUTO_INCREMENT,
login varchar(20) NOT NULL,
pass varchar(255) NOT NULL,
actif tinyint NOT NULL DEFAULT 0,
mail varchar(255) NOT NULL,
signature varchar(100),
date_first timestamp NOT NULL,
date_last timestamp NOT NULL,
avatar_url varchar(255),
grade_id int(11) NOT NULL DEFAULT 0,
anciengrade_id int(11) NOT NULL DEFAULT 0,
PRIMARY KEY(id, grade_id, anciengrade_id),
FOREIGN KEY(grade_id) REFERENCES grades(id),
FOREIGN KEY(anciengrade_id) REFERENCES grades(id))
ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
DROP TABLE IF EXISTS articles;
CREATE TABLE articles(
id int(11) NOT NULL AUTO_INCREMENT,
titre varchar(30) NOT NULL,
sous_titre varchar(60),
contenu text NOT NULL,
membre_id int(11),
source_url varchar(255),
categorie_id int(11),
date_post timestamp NOT NULL,
date_edit timestamp NOT NULL,
publication int(5) NOT NULL DEFAULT 0,
PRIMARY KEY(id, membre_id, categorie_id),
FOREIGN KEY(membre_id) REFERENCES membres(id) ON UPDATE CASCADE ON DELETE SET NULL,
FOREIGN KEY(categorie_id) REFERENCES categories(id) ON UPDATE CASCADE ON DELETE SET NULL)
ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
DROP TABLE IF EXISTS commentaires;
CREATE TABLE commentaires(
id int(11) NOT NULL AUTO_INCREMENT,
membre_id int(11),
date_post timestamp NOT NULL,
contenu text NOT NULL,
article_id int(11),
PRIMARY KEY(id, membre_id, article_id),
FOREIGN KEY(membre_id) REFERENCES membres(id) ON UPDATE CASCADE ON DELETE SET NULL,
FOREIGN KEY(article_id) REFERENCES articles(id) ON UPDATE CASCADE ON DELETE SET NULL)
ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
DROP TABLE IF EXISTS images;
CREATE TABLE images(
id int(11) NOT NULL AUTO_INCREMENT,
nom varchar(20) NOT NULL,
image_url varchar(255) NOT NULL,
width smallint(5) NOT NULL,
height smallint(5) NOT NULL,
extension varchar(5) NOT NULL,
membre_id int(11),
PRIMARY KEY(id, membre_id),
FOREIGN KEY(membre_id) REFERENCES membres(id) ON UPDATE CASCADE ON DELETE SET NULL)
ENGINE=InnoDB DEFAULT CHARSET=latin1;

Et donc en supprimant les clauses de ON UPDATE ON DELETE il n'y a plus de soucie (mais je veux ces clauses :D donc topic pas résolu ;))

PS: si un pro-mysql a quelques suggestion en vue d'optimiser ce fichier je l'écouterai avec attention :)

En remplacant mais ON DELETE SET NULL par des ON DELETE RESTRICT la création fonctionne correctement.

Pourrait-on m'expliquer pourquoi ma clause ON DELETE SET NULL ne fonctionne pas alors que je ne précise, dans la table article par exemple, un categorie_id NOT NULL ?

J'ai vu quelques exemples qui pourtant utilise la clause SET NULL et qui ont l'air d'avoir le même schéma et option que moi :bug: :bug: :bug: :bug: :bug: :bug:

Merci (sujet bientôt clos :D )

En cadeau voici la dernière version du schéma:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
DROP TABLE IF EXISTS categories;
CREATE TABLE categories(
id int(11) NOT NULL AUTO_INCREMENT,
nom varchar(20) NOT NULL,
description varchar(100),
logo_url varchar(255),
PRIMARY KEY(id))
ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
DROP TABLE IF EXISTS grades;
CREATE TABLE grades(
id int(11) NOT NULL AUTO_INCREMENT,
nom varchar(20) NOT NULL,
droit int(11) DEFAULT 0,
PRIMARY KEY(id))
ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
DROP TABLE IF EXISTS membres;
CREATE TABLE membres(
id int(11) NOT NULL AUTO_INCREMENT,
login varchar(20) NOT NULL,
pass varchar(255) NOT NULL,
actif tinyint NOT NULL DEFAULT 0,
mail varchar(255) NOT NULL,
signature varchar(100),
date_first timestamp NOT NULL,
date_last timestamp NOT NULL,
avatar_url varchar(255),
grade_id int(11) NOT NULL DEFAULT 0,
anciengrade_id int(11) NOT NULL DEFAULT 0,
PRIMARY KEY(id, grade_id, anciengrade_id),
FOREIGN KEY(grade_id) REFERENCES grades(id) ON UPDATE CASCADE ON DELETE RESTRICT,
FOREIGN KEY(anciengrade_id) REFERENCES grades(id) ON UPDATE CASCADE ON DELETE RESTRICT)
ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
DROP TABLE IF EXISTS articles;
CREATE TABLE articles(
id int(11) NOT NULL AUTO_INCREMENT,
titre varchar(30) NOT NULL,
sous_titre varchar(60),
contenu text NOT NULL,
membre_id int(11),
source_url varchar(255),
categorie_id int(11),
date_post timestamp NOT NULL,
date_edit timestamp NOT NULL,
publication int(5) NOT NULL DEFAULT 0,
PRIMARY KEY(id, membre_id, categorie_id),
FOREIGN KEY(membre_id) REFERENCES membres(id) ON UPDATE CASCADE ON DELETE RESTRICT,
FOREIGN KEY(categorie_id) REFERENCES categories(id) ON UPDATE CASCADE ON DELETE RESTRICT)
ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
DROP TABLE IF EXISTS commentaires;
CREATE TABLE commentaires(
id int(11) NOT NULL AUTO_INCREMENT,
membre_id int(11),
date_post timestamp NOT NULL,
contenu text NOT NULL,
article_id int(11),
PRIMARY KEY(id, membre_id, article_id),
FOREIGN KEY(membre_id) REFERENCES membres(id) ON UPDATE CASCADE ON DELETE RESTRICT,
FOREIGN KEY(article_id) REFERENCES articles(id) ON UPDATE CASCADE ON DELETE RESTRICT)
ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
DROP TABLE IF EXISTS images;
CREATE TABLE images(
id int(11) NOT NULL AUTO_INCREMENT,
nom varchar(20) NOT NULL,
image_url varchar(255) NOT NULL,
width smallint(5) NOT NULL,
height smallint(5) NOT NULL,
extension varchar(5) NOT NULL,
membre_id int(11),
PRIMARY KEY(id, membre_id),
FOREIGN KEY(membre_id) REFERENCES membres(id) ON UPDATE CASCADE ON DELETE RESTRICT)
ENGINE=InnoDB DEFAULT CHARSET=latin1;

(Je ferai du ménage dans les posts à la résolution de ce sujet :D )

07/03/2007, 14h45
pofexpray

Un petit up!!

Pour l'instant la base fonctionne en mode RESTRICT je ne peux toujours pas la créer avec la clause SET NULL.

Si quelqu'un a une solution, sinon tant pi je clos le sujet demain merci pour le coup de main.
07/03/2007, 16h23
haltabush

:idea:
Je ne suis pas sûr, mais ton membre_id de la base image fait parti de la clef primaire... C'est peut-être lié à ça,non?
08/03/2007, 08h44
pofexpray

C'est vrai que dans toutes mes tables j'ai déclarer plusieurs primary key mais ca n'a pas d'inscidence sur la création (par contre quand je rajouterai des données il risquera de se poser un problème, primary key est unique).

Donc il faut que je change mes primary key superflu en index.

Pour les foreign key je n'ai toujours pas de solution a part mettre les clé en ON DELETE RESTRICT (Si on supprime un membre, tous ces articles ne seront pas modifié, cela me gène un peu mais je règlerai ce problème autrement tant pis).