MySQL。无法创建表 errno 150
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1749332/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL. Can't create table errno 150
提问by David Espart
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.
我必须在 MySQL 中创建一个包含两个表的数据库，但脚本失败并显示 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?
有任何想法吗？
采纳答案by Quassnoi
table1.field1has no index defined on it.
table1.field1没有定义索引。
It is required to place a FOREIGN KEYconstraint on field1.
需要对 进行FOREIGN KEY约束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.
然后一切都应该按预期工作。
回答by Mushtaq Hussain
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 UNSIGNED 而在引用表中它是 INT。
回答by atomice
Depending on the version of MySQL you may need to create an index on table1.field1 first.
根据 MySQL 的版本，您可能需要先在 table1.field1 上创建索引。
回答by jwilm
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
- 引用的主键和引用的外键之间的数据类型不匹配
- 指数。您索引的任何外键都必须为 NOT NULL
回答by Alex
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.
您还需要确保两列具有相同的COLLATION.
回答by Cfreak
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 也会抛出此错误。我把头发拉了一会儿，直到我意识到我错过了一封信foreign key (column1) references mistyped_table(column1)
回答by pl1nk
An option (depending on the case) would be to disable the MySQL integrity check:
一个选项（视情况而定）是禁用 MySQL 完整性检查：
SET FOREIGN_KEY_CHECKS = 0;
回答by Sherlock
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。
回答by Matthias
I got this error while trying to use a foreign key to reference a non-unique field. (which apparentlyis not allowed)
我在尝试使用外键引用非唯一字段时遇到此错误。（这显然是不允许的）
回答by Arvind Bhardwaj
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.
外键名称是现有键的副本。检查您的外键名称在您的数据库中是否唯一。只需在密钥名称的末尾添加一些随机字符即可进行测试。
