MySQL Workbench-正向工程-错误1005:无法创建表(错误号:150) [英] MySQL Workbench - Forward Engineering - Error 1005: Can't create table (errno: 150)

问题描述

我在使用MySQL Workbench 5.2.45的MacOSX上，我已经使用EER Diagram功能设计了一个数据库，然后尝试进行正向工程以生成要放在本地服务器上的数据库.我收到错误代码:错误1005:无法创建表(错误号:150)

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;

我一直在尝试帖子中建议的解决方案(错误代码:1005.无法创建表'...'(errno:150))，但这似乎不是问题. 1.数据类型相同 2.我在引用主键 3.外键名称是唯一的 4.表全部是InnoDB 5.不涉及SET NULL 等

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.

可以帮忙吗?

推荐答案

更改Grant表中的外键约束.

Change the foreign key constraint in Grant table.

引用 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

根据需要命名约束.

这篇关于MySQL Workbench-正向工程-错误1005:无法创建表(错误号:150)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！