Bug Information

Title:  Create table with Foreign keys is buggy
Text:  If you design two tables, 'parents' and 'sons', where 'sons' has a foreign key, and let the program create the sql script, you get this:

-------------------------------------
CREATE TABLE parents(
idparents INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
parent_name Varchar(20) NOT NULL,
PRIMARY KEY(idparents)
)
TYPE=InnoDB;

CREATE TABLE sons(
idsons INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
parents_idparents INTEGER UNSIGNED NOT NULL,
son_name Varchar(20) NOT NULL,
PRIMARY KEY(idsons),
FOREIGN KEY Rel_01(parents_idparents)
REFERENCES parents(idparents)
MATCH FULL
ON DELETE RESTRICT
ON UPDATE RESTRICT
)
TYPE=InnoDB;

-------------------------------------

But if you try to execute this script on mySQL Control Center (I tried with version 4.0.12-nt of mySQL) you get a 150 errornumber.

It's because the script missed to create the index on the sons table!

If you correct the script like this:

-------------------------------------
CREATE TABLE sons(
idsons INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
parents_idparents INTEGER UNSIGNED NOT NULL,
son_name Varchar(20) NOT NULL,
PRIMARY KEY(idsons),
INDEX Rel_01(parents_idparents)
FOREIGN KEY (parents_idparents)
REFERENCES parents(idparents)
MATCH FULL
ON DELETE RESTRICT
ON UPDATE RESTRICT
)
TYPE=InnoDB;
-------------------------------------
i.e. if you first add the INDEX command using the relation_name(field_name) that incorrectly follow the FOREIGN KEY declaration
and remove the relation_name leaving only the (field_name) after the FOREIGN KEY declaration,
then all seems to work fine.
Hoping it can help to improve a already great piece of software,
freedom and greetings to all of you!
Nicoḷ Carandini
Rome, Italy!