Mysql. Can't create table errno 150

Question

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

If nothing works, try this:

The foreign key name is a duplicate of an already existing key. Check that the name of your foreign key is unique within your database. Just add a few random characters to the end of your key name to test for this.

Answer 2

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

Answer 3

An option (depending on the case) would be to disable the mysql integrity check:

SET FOREIGN_KEY_CHECKS = 0;

Answer 4

In my case was probably a server bug dropping a table with the same name. Dropping the whole shcema and re-creating it solved the problem.

Answer 5

In my case, one table was using foreign key constraints on another table that didn't exist yet. This was happening due to a large makefile, so it wasn't as obvious as I would've expected.

Answer 6

In case somebody is still having problems with this, I tried all the solutions above (except for SET FOREIGN_KEY_CHECKS) and nothing worked. The problem was that when you reference the first table, some databases are case sensitive about the table names. I think this is weird since I never saw this before on MySQL, Oracle and now this happened for me on MariaDB.

For example:

Create table if not exists CADASTRO_MAQUINAS ( Id VARCHAR(16), Primary Key (Id) );

Create table if not exists INFOS ( Id_Maquina VARCHAR(16) NOT NULL, CONSTRAINT FK_infos_cadastro_maquinas Foreign Key (Id_Maquina) references CADASTRO_MAQUINAS(Id) );

If I try to create the second table using cadastro_maquinas (lower cases) instead of CADASTRO_MAQUINAS, I will receive this error.