Errno150 Création BDD

Bonjour !

Je rencontre quelques problèmes à l’exécution d'une requête pour la création d'une base de donnée...
Le problème vient apparemment des contraintes de clé étrangères (ça à commencé à planter quand je les ais ajoutés à ma requête) mais je ne comprends pas d’où vient le souci...

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
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
CREATE DATABASE IF NOT EXISTS archivist;
USE archivist;
# -----------------------------------------------------------------------------
# TABLE : SERVER
# -----------------------------------------------------------------------------
 
CREATE TABLE IF NOT EXISTS SERVER
(
ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
ID_SRV_SHOP INT NULL ,
NAME VARCHAR(32) NOT NULL UNIQUE ,
IP VARCHAR(32) NOT NULL ,
TYPE VARCHAR(32) NOT NULL CHECK (TYPE in ("Bank","Server","Database")),
FOREIGN KEY FK_SERVER_SHOP (ID_SRV_SHOP) REFERENCES SHOP (ID)
)
comment = "";
 
# -----------------------------------------------------------------------------
# TABLE : ACCOUNT
# -----------------------------------------------------------------------------
 
CREATE TABLE IF NOT EXISTS ACCOUNT
(
ID INT NOT NULL UNSIGNED AUTO_INCREMENT PRIMARY KEY,
NAME VARCHAR(32) NOT NULL UNIQUE ,
NUM INT NOT NULL ,
IP VARCHAR(32) NOT NULL
)
comment = "";
 
# -----------------------------------------------------------------------------
# TABLE : SCRIPT
# -----------------------------------------------------------------------------
 
CREATE TABLE IF NOT EXISTS SCRIPT
(
ID INT NOT NULL UNSIGNED AUTO_INCREMENT PRIMARY KEY,
NAME VARCHAR(32) NOT NULL UNIQUE ,
VER INT NOT NULL ,
HDD INT NOT NULL ,
TYPE VARCHAR(32) NOT NULL CHECK (TYPE in ("Connection","Memory","Processor","Harddisk")),
CPU INT NOT NULL ,
NOTE VARCHAR(32) NULL
)
comment = "";
 
# -----------------------------------------------------------------------------
# TABLE : HARDWARE
# -----------------------------------------------------------------------------
 
CREATE TABLE IF NOT EXISTS HARDWARE
(
ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
NAME VARCHAR(32) NOT NULL UNIQUE ,
TYPE VARCHAR(32) NOT NULL CHECK (TYPE in ("Modem","Harddisk","Memory","Processor","Plugin")),
RANK INT NOT NULL ,
OUTPUT INT NOT NULL ,
POWERUSE INT NOT NULL ,
NOTE VARCHAR(32) NULL
)
comment = "";
 
# -----------------------------------------------------------------------------
# TABLE : PLAYER
# -----------------------------------------------------------------------------
 
CREATE TABLE IF NOT EXISTS PLAYER
(
ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
NUM INT NOT NULL ,
USERNAME VARCHAR(32) NOT NULL ,
IP VARCHAR(32) NOT NULL UNIQUE ,
NOTE VARCHAR(32) NULL
)
comment = "";
 
# -----------------------------------------------------------------------------
# TABLE : SOFTWARE
# -----------------------------------------------------------------------------
 
CREATE TABLE IF NOT EXISTS SOFTWARE
(
ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
NAME VARCHAR(32) NOT NULL UNIQUE ,
TYPE VARCHAR(32) NOT NULL CHECK (TYPE in ("Shield","System","Password Cracker","Proxy Bypasser","Firewall Bypasser","Decoder","Encryption Cracker","Token","Monitor")),
VER INT NOT NULL ,
HDD INT NOT NULL ,
MEM INT NOT NULL ,
CPU INT NOT NULL ,
NOTE VARCHAR(32) NULL
)
comment = "";
 
# -----------------------------------------------------------------------------
# TABLE : SHOP
# -----------------------------------------------------------------------------
 
CREATE TABLE IF NOT EXISTS SHOP
(
ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
NAME VARCHAR(32) NOT NULL UNIQUE
)
comment = "";
 
# -----------------------------------------------------------------------------
# TABLE : MOTHERBOARD
# -----------------------------------------------------------------------------
 
CREATE TABLE IF NOT EXISTS MOTHERBOARD
(
ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
NAME VARCHAR(32) NOT NULL UNIQUE ,
RANK INT NOT NULL ,
CPU INT NOT NULL ,
MEM INT NOT NULL ,
MODEM INT NOT NULL ,
DISK INT NOT NULL ,
PLUGIN INT NOT NULL ,
POWER INT NOT NULL ,
NOTE VARCHAR(32) NULL
)
comment = "";
 
# -----------------------------------------------------------------------------
# TABLE : SELL_HARDWARE
# -----------------------------------------------------------------------------
 
CREATE TABLE IF NOT EXISTS SELL_HARDWARE
(
ID INT NOT NULL ,
ID_1 INT NOT NULL ,
PRICE REAL(13,2) NULL
, PRIMARY KEY (ID,ID_1),
FOREIGN KEY FK_SELL_HARDWARE_SHOP (ID) REFERENCES SHOP (ID),
FOREIGN KEY FK_SELL_HARDWARE_HARDWARE (ID_1) REFERENCES HARDWARE (ID)
)
comment = "";
 
# -----------------------------------------------------------------------------
# TABLE : SELL_SOFTWARE
# -----------------------------------------------------------------------------
 
CREATE TABLE IF NOT EXISTS SELL_SOFTWARE
(
ID INT NOT NULL ,
ID_1 INT NOT NULL ,
PRICE REAL(13,2) NULL
, PRIMARY KEY (ID,ID_1),
FOREIGN KEY FK_SELL_SOFTWARE_SHOP (ID) REFERENCES SHOP (ID) ,
FOREIGN KEY FK_SELL_SOFTWARE_SOFTWARE (ID_1) REFERENCES SOFTWARE (ID)
)
comment = "";
 
# -----------------------------------------------------------------------------
# TABLE : BANK_ACCOUNT
# -----------------------------------------------------------------------------
 
CREATE TABLE IF NOT EXISTS BANK_ACCOUNT
(
ID INT NOT NULL ,
ID_1 INT NOT NULL
, PRIMARY KEY (ID,ID_1)
FOREIGN KEY FK_BANK_ACCOUNT_ACCOUNT (ID) REFERENCES ACCOUNT (ID) ,
FOREIGN KEY FK_BANK_ACCOUNT_SERVER (ID_1) REFERENCES SERVER (ID)
)
comment = "";
 
# -----------------------------------------------------------------------------
# TABLE : SELL_SCRIPT
# -----------------------------------------------------------------------------
 
CREATE TABLE IF NOT EXISTS SELL_SCRIPT
(
ID INT NOT NULL ,
ID_1 INT NOT NULL ,
PRICE REAL(13,2) NULL
, PRIMARY KEY (ID,ID_1) ,
FOREIGN KEY FK_SELL_SCRIPT_SHOP (ID) REFERENCES SHOP (ID) ,
FOREIGN KEY FK_SELL_SCRIPT_SCRIPT (ID_1) REFERENCES SCRIPT (ID)
)
comment = "";
 
# -----------------------------------------------------------------------------
# TABLE : SELL_MOTHERBOARD
# -----------------------------------------------------------------------------
 
CREATE TABLE IF NOT EXISTS SELL_MOTHERBOARD
(
ID INT NOT NULL ,
IP INT NOT NULL ,
PRICE REAL(13,2) NULL
, PRIMARY KEY (ID,IP) ,
FOREIGN KEY FK_SELL_MOTHERBOARD_SHOP (ID) REFERENCES SHOP (ID) ,
FOREIGN KEY FK_SELL_MOTHERBOARD_MOTHERBOARD (IP) REFERENCES MOTHERBOARD (IP)
)
comment = "";

Voila ce que MySQL me retourne :
#1005 - Can't create table 'archivist.server' (errno: 150)
Une idée du problème ?

Bonjour,

Lisez la doc : http://dev.mysql.com/doc/refman/5.5/...nstraints.html

Et vous comprendrez.

Effectivement en respectant les normes c'est mieux...
Mais j'ai toujours un problème avec cette requête (#1005 - Can't create table 'archivist.sell_scripts' (errno: 150) ) :

Code:

1
2
3
4
5
6
7
8
9
10
 
CREATE TABLE IF NOT EXISTS `sell_scripts`
 (
   `id` INT(8) NOT NULL  ,
   `id_1` INT(8) NOT NULL  ,
   `price` REAL(13,2) NULL , 
   PRIMARY KEY (`id`,`id_1`) ,
   CONSTRAINT FOREIGN KEY FK_SELL_SCRIPT_SHOP (`id`) REFERENCES SHOP (`id`) ,
   CONSTRAINT FOREIGN KEY FK_SELL_SCRIPT_SCRIPT (`id_1`) REFERENCES SCRIPT (`id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Bon je quote :

Citation:

If MySQL reports an error number 1005 from a CREATE TABLE statement, and the error message refers to error 150, table creation failed because a foreign key constraint was not correctly formed. Similarly, if an ALTER TABLE fails and it refers to error 150, that means a foreign key definition would be incorrectly formed for the altered table. You can use SHOW ENGINE INNODB STATUS to display a detailed explanation of the most recent InnoDB foreign key error in the server.

=> vous essayez de créer une table qui a une fk qui ne pointe sur rien.
Revoyez l'ordre de création des tables