MySQL Forums
Forum List  »  General

ERROR 1005 (HY000): Can't create table ... (errno: 150 )
Posted by: Sam Ohn
Date: December 13, 2010 07:36PM

Hi all,

I want to use Mysql Workbench, and I created a test database using Workbench which has two simple tables, employee and borrowed.
Employee's id is referenced for foreign key by a foreign key field, employeeid on borrowed. I used the Model tool and Workbench generated following sql statements which could create employee table, but returned ERROR to create borrowed table --so borrowed table could not be created:

ERROR 1005 (HY000): Can't create table 'temployeeborrowed2.borrowed' (errno: 150)

I actuall manually created identical database (please see 2nd. code below) and run it on CMD, and it created borrowed table w/o errors.


One funny thing I discovered was that the table generated on server through Workbench does not seem to be exactly the same as the SQL statement generated by Workbench.

The actual employee table created on server through Workbench is:
CREATE TABLE `employee` (
  `idemployee` int(11) NOT NULL,
  `first name` varchar(45) DEFAULT NULL,
  `last name` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`idemployee`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

According to the code generated by Workbench, idemployee's data type on borrowed table should be small int but, actually the sql script Workbench created set int(11) for ideemployee field data type on the server. If the data type of referenced field is different between parent and child table, it is problem....
So, I manually changed the data type of referencing filed on borrowed table in the CREATE statement as:
employeeid small int ---> employeeid int(11)
and run the create statement but it still returned the same error.

Does Anyone know what caused this error?

I will appreciate any comments and suggestions.

Thank you in advance

Sam




The code generated by Workbench and returned error to create borrowed table:
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 `temployeeborrowed2` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `temployeeborrowed2` ;

-- -----------------------------------------------------
-- Table `temployeeborrowed2`.`employee`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `temployeeborrowed2`.`employee` (
  `idemployee` SMALLINT NOT NULL ,
  `first name` VARCHAR(45) NULL ,
  `last name` VARCHAR(45) NULL ,
  PRIMARY KEY (`idemployee`) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;

-- -----------------------------------------------------
-- Table `temployeeborrowed2`.`borrowed`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `temployeeborrowed2`.`borrowed` (
  `ref` INT NOT NULL AUTO_INCREMENT ,
  `book` VARCHAR(45) NULL ,
  `employeeid` SMALLINT NOT NULL ,
  PRIMARY KEY (`ref`) ,
  INDEX `fk_borrowed_employee` (`employeeid` ASC) ,
  CONSTRAINT `fk_borrowed_employee`
    FOREIGN KEY (`employeeid` )
    REFERENCES `temployeeborrowed2`.`employee` (`idemployee` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

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

The SQL Statement I created by hand for creating employee and borrowed tables:
CREATE TABLE `employee` (
  `id` smallint(5) unsigned NOT NULL,
  `firstname` varchar(30) DEFAULT NULL,
  `lastname` varchar(30) DEFAULT NULL,
  `birthdate` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

CREATE TABLE `borrowed` (
  `ref` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `employeeid` smallint(5) unsigned NOT NULL,
  `book` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`ref`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8

Options: ReplyQuote


Subject
Written By
Posted
ERROR 1005 (HY000): Can't create table ... (errno: 150 )
December 13, 2010 07:36PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.