Mysql。不能创建表格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.

我必须在mysql中创建一个有两个表的db，但是脚本不能使用errno 150(外键问题)。我再次检查了两个表上的外键字段是相同的，我找不到任何错误。

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.

我在Windows和Ubuntu中试用过不同版本的Mysql，但没有成功。

Any ideas? Thanks a lot.

什么好主意吗?非常感谢。

22 个解决方案

#1

table1.field1 has no index defined on it.

表1。field1没有在其上定义索引。

It is required to place a FOREIGN KEY constraint on field1.

需要在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.

一切都应按预期工作。

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

在使用MySQL Workbench和MySQL 5.5.27时，我遇到了类似的问题。在我的案例中，问题是INT类型字段。错误地在一个表中，它是未签名的，在引用表中是INT。

#3

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

根据MySQL版本，您可能需要在table1上创建索引。field1第一。

#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
• 指数。您索引的任何外键都必须不是空的。

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

即使您的数据类型看起来是相同的——在我的情况下，两列都有VARCHAR(50)——这还不够。

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

您还需要确保两列都具有相同的排序规则。

#6

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

一个选项(取决于具体情况)是禁用mysql完整性检查:

SET FOREIGN_KEY_CHECKS = 0;

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

另一个原因，尽管有点类似于其他原因:我指的是一个表，结果显示是MyISAM引擎，而不是InnoDB。

#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)

如果您的错误输入引用表的名称，MySQL也会抛出这个错误。我把头发拉了一段时间，直到我意识到我漏掉了一个外键(column1)的字母(column1)

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

外键名是已经存在的密钥的副本。检查您的外键名称在数据库中是唯一的。只需在您的密钥名末尾添加一些随机字符，以进行测试。

#10

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

总是先创建主/父表，然后创建细节/子表。

#11

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.

在我的例子中，有一个表在另一个表上使用了外键约束，而这个表还不存在。这是由于一个大的makefile，所以它不像我期望的那样明显。

#12

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.

如果有人仍然有问题，我尝试了上面所有的解决方案(除了设置了foreign_key_check)，没有任何工作。问题是，当您引用第一个表时，一些数据库对表名是大小写敏感的。我觉得这很奇怪，因为我以前从来没有在MySQL, Oracle上看到过，现在这在MariaDB上发生了。

For example:

例如:

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

创建表，如果不存在CADASTRO_MAQUINAS (Id VARCHAR(16)，主键(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) );

创建表如果不存在INFOS (Id_Maquina VARCHAR(16) not NULL，约束FK_infos_cadastro_maquinas外键(Id_Maquina)引用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.

如果我尝试用cadastro_maquinas(小写)来创建第二个表，而不是cadastro_maquinas，我将会收到这个错误。

#13

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.

我使用的是MySQL工作台。问题是您不能使用相同的外键名称，它们需要是唯一的。因此，如果多个表将引用相同的外键，那么每次都必须有一个惟一的名称。

#14

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)

1、检查第2列的情况，检查第3列的排序，检查是否在两个表中都创建了一个键(唯一的、主的)

#15

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.

在我的例子中，我在其中一个表中找到了MyISAM的旧表定义，显然我无法从另一个表中提取外键。也许这帮助别人。

So this may happen because of inconsistencies between two databases/fields definitions try to check:

因此，这可能是因为两个数据库/字段定义之间的不一致，试图检查:

Field Type
Field Collation
Table Engine

#16

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

对我来说，问题是在CREATE TABLE查询中使用约束。

#17

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.

在这种情况下，在FK定义中使用article_id和type字段非常重要，因为它们出现在article表主键定义中。

#18

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.

在我的例子中，可能是服务器错误删除了一个同名的表。去掉整个shcema并重新创建它解决了问题。

#19

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

结果显示，innoDB被破坏了，请看:https://dba.stackexchange.com/questions/86853/1025-error-on- of-table- error- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

#20

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.

如果您正在使用mysql工作台，并且您在关系表中得到这个错误，那么可能会有一个快速修复:只需删除它，并让mysql工作台为您重新创建它。然后复制sql。修正了我的错误150问题。

#21

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

在尝试使用外键引用非唯一字段时，我得到了这个错误。(显然这是不允许的)

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

当我遇到这个问题时，是因为我在第一个表中设置了未签名的id，而第二个表中的外键不是。让他们两个都没有签名，替我把它修好。