MySQL Bugs: #32764: Forward Engineer fails to create foreign key constraints

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

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.

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.

Thank you for a problem report. Please, try to repeat with a newer version, 5.0.10, and inform about the results.

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".

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)

I've tried to generate CREATE TABLE script skiping Foreign Key constraints, but checking the option had no effects.

I installed the last version of MySQL and I have the same issue, and no solutions since 2007??

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.