Bug #32764 Forward Engineer fails to create foreign key constraints
Submitted: 27 Nov 2007 11:43 Modified: 15 May 2014 22:33
Reporter: Daniel Haas Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Workbench Severity:S2 (Serious)
Version:5.0.9 OS:Windows (Vista)
Assigned to: CPU Architecture:Any

[27 Nov 2007 11:43] Daniel Haas
Description:
When generating the database tables of a simple model with to referencing tables, the MySQL error "Error 1005: Can't create table '.\mydb\customers.frm' (errno: 150)" occurs.

An SHOW ENGINE INNODB STATUS; reveals the following:

------------------------
LATEST FOREIGN KEY ERROR
------------------------
071127 12:29:14 Error in foreign key constraint of table mydb/users:
there is no index in referenced table which would contain
the columns as the first columns, or the data types in the
referenced table do not match to the ones in table. Constraint:
,
  CONSTRAINT customer FOREIGN KEY (customer_id) REFERENCES customers (id) ON DELETE NO ACTION ON UPDATE NO ACTION
The index in the foreign key in table is customer_id
See http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html
for correct foreign key definition.

I tried to overcome the problem with various things:
1. Checked all table and field names
2. First I changed the order in which the tables where created (1. customer, 2. user)
3. Ensured that the tables are InnoDB.
4. Explicitly set the length of the id fields to 11 ( INT(11) )
5. As mentioned in the error, added an explicit index on the id field (allthough) not necessary since its the primary key anyway.

Unfortunatly nothing helped to solve the problem. Since I am new to foreign key constraints it may be possible that i did something wrong, but then again it should not be possible to create bogus foreign key constraints in a gui application.

For reference i will attach the model created with MySQL Workbench that shows this behaviour.

How to repeat:
1. Load the attached model
2. Use Database -> Forward Engineer ...
3. Don't change any options in the wizard
4. Follow the steps of the wizard and enter you database connection
5. Try to create the tables and watch the error.

Suggested fix:
This should work out of the box, if the model is ok.
If the model is not ok, it would be nice if the Forward Engineer wizard would show the output of "SHOW ENGINE INNODB STATUS" to help in diagnosing the error.
[27 Nov 2007 11:48] Daniel Haas
The example model which shows the error. - Since Vista always crashes on save i had to convert the dump-file to xml.

Attachment: foreign-key-model.xml (text/xml), 49.07 KiB.

[1 Dec 2007 7:39] Valeriy Kravchuk
Thank you for a problem report. Please, try to repeat with a newer version, 5.0.10, and inform about the results.
[2 Jan 2008 0:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[28 Jan 2010 15:13] Julien MAITAN
I ran into a similar error using 5.2.14 OSS beta Revision 4990 on WinXP Pro

Here is ths SQL statement generated with the forward engineering command :

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';

CREATE SCHEMA IF NOT EXISTS `sfotelor` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;

-- -----------------------------------------------------
-- Table `sfotelor`.`liste_departement` --
-- ---------------------------------------------------
CREATE  TABLE IF NOT EXISTS `sfotelor`.`liste_departement` (
  `num_dpt` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT ,
  `lib_dpt` MEDIUMTEXT NOT NULL ,
  PRIMARY KEY (`num_dpt`) )
ENGINE = InnoDB
AUTO_INCREMENT = 89
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;

-- -----------------------------------------------------
-- Table `sfotelor`.`liste_canton`
-- -----------------------------------------------------

CREATE  TABLE IF NOT EXISTS `sfotelor`.`liste_canton` (
  `num_canton` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT ,
  `lib_canton` MEDIUMTEXT NOT NULL ,
  `num_dpt` BIGINT(20) NOT NULL DEFAULT '0' ,
  PRIMARY KEY (`num_canton`) ,
  INDEX `fk_liste_canton_liste_departement1` (`num_dpt` ASC) ,
  CONSTRAINT `fk_liste_canton_liste_departement1`
    FOREIGN KEY (`num_dpt` )
    REFERENCES `sfotelor`.`liste_departement` (`num_dpt` )
    ON DELETE NO ACTION
    ON UPDATE CASCADE)
ENGINE = InnoDB
AUTO_INCREMENT = 88100
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;

And here is the error log :
Executing SQL script in server
ERROR: Error 1005: Ne peut cr�er la table 'sfotelor.liste_canton' (Errcode: 150)
[28 Jan 2010 15:33] Julien MAITAN
I've tried to generate CREATE TABLE script skiping Foreign Key constraints, but checking the option had no effects.
[2 May 2014 18:54] Wilfredo Gutierrez Ramirez
I installed the last version of MySQL and I have the same issue, and no solutions since 2007??
[15 May 2014 22:33] Alfredo Kojima
The problem is because your primary key and foreign key types are not identical (in the examples pasted, the PK is UNSIGNED and the FK is not.