MySQL Workbench CE to Forward Engineering to MySQL DB errors

[jkoritzinsky16]

I use MySQL Workbench Community Edition to set up my databases before I create them. I just finished my setup and I am trying to build the tables. So far I have successfully created all of my tables and routines except for two of them, my `Planets` table and my `Designs` table. The following is the only error that I get (in this form, I am unable to click the link):

#1005 - Can't create table './jkor99_global/Designs.frm' (errno: 150) (<a href="server_engines.php?engine=InnoDB&amp;page=Status&amp;token=e924f07e856b07a28daed6a39edc1e87">Details...</a>)

This is the SQL code for my planets table:

CREATE  TABLE IF NOT EXISTS `Planets` (
  `PlanetID` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `PlanetDisplayNumb` INT NOT NULL ,
  `SolarSystem` INT NOT NULL ,
  `XCoord` INT NOT NULL ,
  `YCoord` INT NOT NULL ,
  `ZCoord` INT NOT NULL ,
  PRIMARY KEY (`PlanetID`) ,
  UNIQUE INDEX `LocationUnique` (`XCoord` ASC, `YCoord` ASC, `ZCoord` ASC, `SolarSystem` ASC) ,
  UNIQUE INDEX `DispNameUnique` (`PlanetDisplayNumb` ASC, `SolarSystem` ASC) ,
  INDEX `fk_Planets_SolarSystems1` (`SolarSystem` ASC) ,
  CONSTRAINT `fk_Planets_SolarSystems1`
    FOREIGN KEY (`SolarSystem` )
    REFERENCES `SolarSystems` (`SSID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

This is the code for my designs table:

CREATE  TABLE IF NOT EXISTS `Designs` (
  `DesignID` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `Hull` INT NOT NULL ,
  `Modules` VARCHAR(255) NOT NULL ,
  `Description` MEDIUMTEXT NOT NULL ,
  PRIMARY KEY (`DesignID`) ,
  INDEX `fk_Designs_Hulls` (`Hull` ASC) ,
  UNIQUE INDEX `DesignUniqueness` (`Hull` ASC, `Modules` ASC) ,
  CONSTRAINT `fk_Designs_Hulls`
    FOREIGN KEY (`Hull` )
    REFERENCES `Hulls` (`HullID` )
    ON DELETE RESTRICT
    ON UPDATE CASCADE)
ENGINE = InnoDB;

Any ideas?

 

[misson]

What are the create statements for SolarSystem and Hulls?

Error number 150 means there was a problem with the foreign key. Is Hulls an InnoDB table? If it's a MyISAM table, that will cause just such an error.

As for the Planets table, there might be a similar problem with the SolarSystem table. Without getting an error message about the Planets table creation, it's hard to say what (if anything) could be going wrong.

 

[jkoritzinsky16]

All of my tables are InnoDB tables and both error messages for the tables are exactly the same (except for the table name of course).

 

[descalzo]

INDEX `fk_Planets_SolarSystems1` (`SolarSystem` ASC) ,
  CONSTRAINT `fk_Planets_SolarSystems1`

Why did you give them the same name?

 

[jkoritzinsky16]

That is the way the script was generated from MySQL Workbench, and I saw no reason to change it. Do you think that is the problem?

Edit: I just changed the names and ran the query again and it still failed.

 

[misson]

That is the way the script was generated from MySQL Workbench, and I saw no reason to change it. Do you think that is the problem?

I had thought so myself after reading Descalzo's response, but removing the foreign key name didn't help when I finally tried it.

I notice your primary key columns are unsigned, but the foreign key columns aren't. If SolarSystem.SSID and Hulls.HullID are unsigned as well, this will cause the foreign key definition to fail. This sort of thing is one reason I want to see the creation statements for SolarSystem and Hulls (another is so the example is complete). Either remove the "UNSIGNED" attribute from all primary key columns or add it to the foreign key columns.

As a side note, the inaccurate error message when column types don't match is listed as a bug in MySQL's bug system. A fix now exists for MySQL 5.5, though this is of no help to the many of us running 5.1.

 

[jkoritzinsky16]

THAT would explain it... I thought for sure I fixed the unsigned/signed problem in MySQL Workbench, let me check...
Yep, now it works. Thanks to everyone who helped me!