MySQL Workbench - Forward Engineering - Error 1005: Can't create table (errno: 150)

Question

I am on MacOSX using MySQL Workbench 5.2.45 I have designed a database using the EER Diagram functionality and then tried the forward engineering to generate my database to put on my local server. I get the error code: Error 1005: Can't create table (errno: 150)

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,ALLOW_INVALID_DATES';

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

-- -----------------------------------------------------
-- Table `Finance`.`DatabaseUser`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `Finance`.`DatabaseUser` (
  `ID` INT(11) NOT NULL AUTO_INCREMENT ,
  `FirstName` VARCHAR(255) NOT NULL ,
  `LastName` VARCHAR(255) NOT NULL ,
  `Tel` VARCHAR(255) NULL ,
  `Email` VARCHAR(255) NOT NULL,
  `Password` VARCHAR(255) NOT NULL ,
  `Admin` CHAR(1) NOT NULL DEFAULT 'N' ,
  `Project1` CHAR(1) NOT NULL DEFAULT 'N' ,
  `Project2` CHAR(1) NOT NULL DEFAULT 'N' ,
  `Project3` CHAR(1) NOT NULL DEFAULT 'N' ,
  PRIMARY KEY (`ID`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `Finance`.`Entities`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `Finance`.`Entities` (
  `ID` INT(11) NOT NULL AUTO_INCREMENT ,
  `CreatorID` INT(11) NOT NULL ,
  `FullName` VARCHAR(255) NOT NULL ,
  `ShortName` VARCHAR(255) NULL ,
  PRIMARY KEY (`ID`) ,
  INDEX `Creator_idx` (`CreatorID` ASC) ,
  CONSTRAINT `CreatorEntities`
    FOREIGN KEY (`CreatorID` )
    REFERENCES `Finance`.`DatabaseUser` (`ID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `Finance`.`Grant`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `Finance`.`Grant` (
  `ID` INT(11) NOT NULL AUTO_INCREMENT ,
  `CreatorID` INT(11) NOT NULL ,
  `DonorID` INT(11) NOT NULL ,
  `RecipientID` INT(11) NOT NULL ,
  `Name` VARCHAR(255) NOT NULL ,
  `Year` YEAR NOT NULL ,
  PRIMARY KEY (`ID`) ,
  INDEX `Creator_idx` (`CreatorID` ASC) ,
  INDEX `index3` (`DonorID` ASC, `RecipientID` ASC) ,
  CONSTRAINT `CreatorGrant`
    FOREIGN KEY (`CreatorID` )
    REFERENCES `Finance`.`DatabaseUser` (`ID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `InstrumentGrant`
    FOREIGN KEY (`DonorID` , `RecipientID` )
    REFERENCES `Finance`.`Entities` (`ID` , `ID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

USE `Finance` ;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

I have been trying the solutions suggested in the post (Error Code: 1005. Can't create table '...' (errno: 150)) but this doesn't seem to be the problem. 1. The datatypes are the same 2. I'm referencing primary keys 3. Foreign key names are unique 4. Tables are all InnoDB 5. No SET NULL involved etc.

Can you please help?

which of the FK constraints is failing? and `show engine innodb status` will dump out a ton of data, buried in the middle of which is "last foreign key error". it'll have more details about why you're getting the 1005/150. — Marc B, Feb 21 '13 at 22:09
Sorry forgot to mention this 'ERROR: Error 1005: Can't create table 'Finance.Grant' (errno: 150)' — FinanceGardener, Feb 21 '13 at 22:11
and looking at the syntax, the `(ID, ID)` bit in the Grant table's InstrumentGrant FK looks funky. not sure you can FK to the same field twice like that. — Marc B, Feb 21 '13 at 22:15
@MarcB Thanks for this. I don't know how / where to do 'show engine innodb status' in MySQL Workbench. — FinanceGardener, Feb 21 '13 at 22:24

score 1 · Accepted Answer · answered Feb 21 '13 at 22:23

1

Change the foreign key constraint in Grant table.

Refer http://www.sqlfiddle.com/#!2/04945

  CONSTRAINT `InstrumentGrant`
    FOREIGN KEY (`RecipientID`)
    REFERENCES `Entities` (`ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `InstrumentGrant_2`
    FOREIGN KEY (`DonorID`)
    REFERENCES `Entities` (`ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION

Name the constraint as you want.

answered Feb 21 '13 at 22:23

Slowcoder

2,060
3
16
21

@MarcB and Slowcoder Many thanks that solved my problem! No FK to the same field twice in a single constraint. – FinanceGardener Feb 21 '13 at 22:32

score 0 · Answer 2 · answered Oct 16 '13 at 06:03

0

Resolved:

In export menu, select "Generate Separate CREATE INDEX Statements" option in order to avoid this error.

answered Oct 16 '13 at 06:03

Makhz

65
1
8

MySQL Workbench - Forward Engineering - Error 1005: Can't create table (errno: 150)

2 Answers2

Linked