MySQL. Can't create table errno 150

Question

I have to create a database with two tables in MySQL, but the script fails with errno 150 (foreign key problem). I double-checked the foreign key fields to be the same on both tables, and I can't find any error.

Here is the script:

 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';   DROP SCHEMA IF EXISTS `testdb`;  CREATE SCHEMA IF NOT EXISTS `testdb` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;  USE `testdb`;   DROP TABLE IF EXISTS `testdb`.`table1` ;   CREATE  TABLE IF NOT EXISTS `testdb`.`table1` (    `id` INT UNSIGNED NOT NULL ,    `field1` VARCHAR(50) NULL ,    PRIMARY KEY (`id`) )   ENGINE = InnoDB;    DROP TABLE IF EXISTS `testdb`.`table2` ;   CREATE  TABLE IF NOT EXISTS `testdb`.`table2` (    `id` INT NOT NULL AUTO_INCREMENT ,    `field1` VARCHAR(50) NULL ,    `date` DATE NULL ,    `cnt` INT NULL ,    PRIMARY KEY (`id`) ,    INDEX `FK_table2_table1` (`field1` ASC) ,    CONSTRAINT `FK_table2_table1`    FOREIGN KEY (`field1`)    REFERENCES `testdb`.`table1` (`field1` )    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; 

I've tried it in Windows and Ubuntu with different versions of MySQL and didn't work.

Any ideas?

Answer 1

table1.field1 has no index defined on it.

It is required to place a FOREIGN KEY constraint on field1.

With this:

 CREATE  TABLE IF NOT EXISTS `testdb`.`table1` (    `id` INT UNSIGNED NOT NULL ,    `field1` VARCHAR(50) NULL ,    KEY ix_table1_field1 (field1),    PRIMARY KEY (`id`) )  ENGINE = InnoDB; 

Everything should then work as expected.

Answer 2

While working with MySQL Workbench and MySQL 5.5.27, I have encountered the similar problem. In my case issue was with INT type fields. Erroneously in one table it was INT UNSIGNED and in referencing table it was INT.