MySQL. Can't create table errno 150

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?

Solution 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.

Solution 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.

Solution 3:

Depending on the version of MySQL you may need to create an index on table1.field1 first.

Solution 4:

One of the answers here suggests to disable the foreign key integrity check. This is a BAD idea. There are two likely culprits here:

• Data type mismatch between referenced primary key and referencing foreign key
• Indices. Any foreign keys which you index must be NOT NULL