Mysql: Can't create table errno 150

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';



 CREATE SCHEMA IF NOT EXISTS `testdb` DEFAULT CHARACTER 

SET utf8 COLLATE utf8_unicode_ci ; USE `testdb`;

 DROP SCHEMA IF EXISTS `testdb`;




 DROP TABLE IF EXISTS `testdb`.`table1` ;



 CREATE  TABLE IF NOT EXISTS `testdb`.`table1` (

   `id` INT UNSIGNED NOT NULL ,

   PRIMARY KEY (`id`) )

   `field1` VARCHAR(50) NULL ,

 ENGINE = InnoDB;



 CREATE  TABLE IF NOT EXISTS `testdb`.`table2` (

 DROP TABLE IF EXISTS `testdb`.`table2` ;



   `id` INT NOT NULL AUTO_INCREMENT ,

   PRIMARY KEY (`id`) ,

   `field1` VARCHAR(50) NULL ,

   `date` DATE NULL ,

  REFERENCES `testdb`.`table1` (`field1` )

   `cnt` INT NULL ,

   INDEX `FK_table2_table1` (`field1` ASC) ,

  FOREIGN KEY (`field1`)

   CONSTRAINT `FK_table2_table1`

 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;

  ON DELETE NO ACTION

  ON UPDATE NO ACTION)

 ENGINE = InnoDB;



 SET SQL_MODE=@OLD_SQL_MODE;

 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

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

 Answers

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.

---

Depending on the version of MySQL you may need to create an index 

on table1.field1 first.

---

Another hint:

Even when your data types seem to be the same - in my case both 

columns had VARCHAR(50) - this is not enough.

You also need to make sure that both columns have the same COLLATION.

---

Yet another cause, although slightly similar to others: I was referring to 

a table that turned out to have the MyISAM engine, instead of InnoDB.

---

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.

---

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.

---

I was using MySQL workBench. THe issue is you cannot use the same 

foreign key name, they need to be unique. So if more than one 

table will reference the same foreign key, each time there must be a 

unique name given.

---

In my case I got old table definition MyISAM in one of the tables and 

obviously I was unable to make foreign key to it from another table. 

Maybe this help someone.

So this may happen because of inconsistencies between two databases/fields 

definitions try to check:

Field Type
Field Collation
Table Engine

---

You also may encounter the same error when attempting to reference a 

composite key in your foreign key.

For example:

CREATE TABLE `article` (

`id` int(10) unsigned NOT NULL,

`type` enum('X','Y','Z') NOT NULL,

PRIMARY KEY (`id`,`type`)

CREATE TABLE `t1` (

) ENGINE InnoDB; 

`type` enum('X','Y','Z') NOT NULL,

`user_id` int(10) unsigned NOT NULL,

CONSTRAINT `user_access_article_ibfk_2` FOREIGN KEY (`article_id`, `type`) 

REFERENCES `article` (`id`, `type`) ON DELETE CASCADE ON UPDATE CASCADE

`article_id` int(10) unsigned NOT NULL,

) ENGINE=InnoDB

In this case, it is important to use article_id and type field in the FK 

definition in the very same order as they appear in the article table 

PRIMARY KEY definition.

---

In very strange cases your database might be broken. In my case 

I did not have any foreign keys on the table and the only renaming 

the table or changing engine helped.

Turned out innoDB was broken, see: 

https://dba.stackexchange.com/questions/86853/1025-error-on-rename-of-table-errno-150-table-was-deleted-while-tried-to-a?lq=1

---

I got this error while trying to use a foreign key to reference a non-unique field. 

(which apparently is not allowed)