ERROR 1005 (HY000): Can't create table ... (errno: 150 )
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