Mysql. Can't create table errno 150

Question

Hello, I have to create a db with 2 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? Thanks a lot.

Answer 1

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

Answer 2

table1.field1 has no index defined on it.

It is required to police the FOREIGN KEY constraint.

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