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?

Obsolescent

I also received this error by attempting to create a table with a FK constraint to another table that did not yet exist. It's an easy problem to fix, made very difficult by a completely useless error message. – Jamaaljamaica

For those getting here from google, this error can also happen if you make a typo on the foreign key's table name. – Cheju

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.

Headspring

Although it doesn't apply in this case, you will get the same error message if you try to add a foreign key constraint and on delete set to null for a non-nullable column. – Anfractuous

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.

Applique

Answer 3

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

Supination

Answer 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

Penta

The NOT NULL argument doesn't apply on my Mysql 5.5 – Hileman

Answer 5

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.

Phototelegraph

Answer 6

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

SET FOREIGN_KEY_CHECKS = 0;

Rapture

Answer 7

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.

Pyrotechnic

Answer 8

MySQL will also throw this error if your mistyping the name of the referring table. I pulled my hair out for awhile until I realized I missed a letter in foreign key (column1) references mistyped_table(column1)

Gable

An in some situations, case (of table name) matters - in some not. For example, my script worked on mysql on OSX, but on linux I had this errno: 150 problem. Case was my issue. – Blumenthal

Similarly, I had FOREIGN KEY (user_id) REFERENCES user(id) ON DELETE CASCADE, but the user table has no field id -- it was called user_id! So in the above I changed user(id) to user(user_id), and all was well in the universe again... for now. – Earshot

Answer 9

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.

Matlick

Answer 10

I got this error while trying to use a foreign key to reference a non-unique field. (which apparently is not allowed)

Repent

Answer 11

Always create master/parent tables first, and then create your detail/child tables.

Deoxyribose

Answer 12

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.

Farmstead

Answer 13

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.

Cairistiona

Answer 14

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.

Aconcagua

Answer 15

I had a similar error on one of my tables. When checked column Collation was different, which worked once changed both columns to the same Collation type.

After reading most of the suggested solution here. I just thought it might be helpful if I just list down all the possibilities which could throw this error.

1, Check CASE of the Column 2, Check COLLATION of Columns 3, Check if there is a key created in both tables for the column (Unique, Primary)

Bearing

Answer 16

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

Gavel

Answer 17

For me, the problem was with using CONSTRAINT in the CREATE TABLE query.

Harrington

Answer 18

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`)
) ENGINE InnoDB;

CREATE TABLE `t1` (
  `user_id` int(10) unsigned NOT NULL,
  `type` enum('X','Y','Z') NOT NULL,
  `article_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
) 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.

Excurvature

Answer 19

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.

Nonjuror

Answer 20

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

Cone

Answer 21

If you are working on mysql workbench and you get this error for a relationship table there may be a quick fix for you: just delete it and let mysql workbench recreate it for you. Then copy the sql. Fixed my errno 150 problem.

Pestle

Answer 22

When I had this problem it was because I had set the id in the first table to be unsigned whereas the foreign key in the second table was not. Making them both unsigned fixed it for me.

Demonolatry