MySQL：创建带有 FK 错误的表 (errno 150)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1085001/
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: Creating table with FK error (errno 150)
提问by Peter Bailey
I've created a model with MySQL Workbenchand am now attempting to install it to a mysql server.
我已经使用MySQL Workbench创建了一个模型，现在正在尝试将其安装到 mysql 服务器。
Using File > Export > Forward Engineer SQL CREATE Script...it outputs a nice big file for me, with all the settings I ask for. I switch over to MySQL GUI Tools(the Query Browser specifically) and load up this script (note that I'm going form one official MySQL tool to another). However, when I try to actually execute this file, I get the same error over and over
使用File > Export > Forward Engineer SQL CREATE Script ...它为我输出了一个不错的大文件，其中包含我要求的所有设置。我切换到MySQL GUI 工具（特别是查询浏览器）并加载这个脚本（请注意，我将从一个官方 MySQL 工具转换为另一个）。但是，当我尝试实际执行此文件时，我一遍又一遍地收到相同的错误
SQLSTATE[HY000]: General error: 1005 Can't create table './srs_dev/location.frm' (errno: 150)
SQLSTATE[HY000]：一般错误：1005 无法创建表 './srs_dev/location.frm' (errno: 150)
"OK", I say to myself, something is wrong with the location table. So I check out the definition in the output file.
“好的”，我对自己说，位置表有问题。所以我检查了输出文件中的定义。
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';
-- -----------------------------------------------------
-- Table `state`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `state` ;
CREATE TABLE IF NOT EXISTS `state` (
`state_id` INT NOT NULL AUTO_INCREMENT ,
`iso_3166_2_code` VARCHAR(2) NOT NULL ,
`name` VARCHAR(60) NOT NULL ,
PRIMARY KEY (`state_id`) )
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `brand`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `brand` ;
CREATE TABLE IF NOT EXISTS `brand` (
`brand_id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`name` VARCHAR(45) NOT NULL ,
`domain` VARCHAR(45) NOT NULL ,
`manager_name` VARCHAR(100) NULL ,
`manager_email` VARCHAR(255) NULL ,
PRIMARY KEY (`brand_id`) )
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `location`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `location` ;
CREATE TABLE IF NOT EXISTS `location` (
`location_id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`name` VARCHAR(255) NOT NULL ,
`address_line_1` VARCHAR(255) NULL ,
`address_line_2` VARCHAR(255) NULL ,
`city` VARCHAR(100) NULL ,
`state_id` TINYINT UNSIGNED NULL DEFAULT NULL ,
`postal_code` VARCHAR(10) NULL ,
`phone_number` VARCHAR(20) NULL ,
`fax_number` VARCHAR(20) NULL ,
`lat` DECIMAL(9,6) NOT NULL ,
`lng` DECIMAL(9,6) NOT NULL ,
`contact_url` VARCHAR(255) NULL ,
`brand_id` TINYINT UNSIGNED NOT NULL ,
`summer_hours` VARCHAR(255) NULL ,
`winter_hours` VARCHAR(255) NULL ,
`after_hours_emergency` VARCHAR(255) NULL ,
`image_file_name` VARCHAR(100) NULL ,
`manager_name` VARCHAR(100) NULL ,
`manager_email` VARCHAR(255) NULL ,
`created_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
PRIMARY KEY (`location_id`) ,
CONSTRAINT `fk_location_state`
FOREIGN KEY (`state_id` )
REFERENCES `state` (`state_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_location_brand`
FOREIGN KEY (`brand_id` )
REFERENCES `brand` (`brand_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
CREATE INDEX `fk_location_state` ON `location` (`state_id` ASC) ;
CREATE INDEX `fk_location_brand` ON `location` (`brand_id` ASC) ;
CREATE INDEX `idx_lat` ON `location` (`lat` ASC) ;
CREATE INDEX `idx_lng` ON `location` (`lng` ASC) ;
Looks ok to me. I surmise that maybe something is wrong with the Query Browser, so I put this file on the server and try to load it this way
对我来说看起来没问题。我猜测查询浏览器可能有问题，所以我将此文件放在服务器上并尝试以这种方式加载它
] mysql -u admin -p -D dbname < path/to/create_file.sql
And I get the same error. So I start to Google this issue and find all kinds of accounts that talk about an error with InnoDB style tables that fail with foreign keys, and the fix is to add "SET FOREIGN_KEY_CHECKS=0;" to the SQL script. Well, as you can see, that's already part of the file that MySQL Workbench spat out.
我得到了同样的错误。所以我开始在谷歌上搜索这个问题并找到各种谈论 InnoDB 样式表错误的帐户，这些表因外键而失败，解决方法是添加“SET FOREIGN_KEY_CHECKS=0;” 到 SQL 脚本。好吧，正如您所看到的，这已经是 MySQL Workbench 输出的文件的一部分。
So, my question is then, why is this not working when I'm doing what I think I'm supposed to be doing?
所以，我的问题是，当我在做我认为我应该做的事情时，为什么这不起作用？
Version Info:
版本信息：
MySQL: 5.0.45
- GUI Tools: 1.2.17
- Workbench: 5.0.30
MySQL：5.0.45
- 图形用户界面工具：1.2.17
- 工作台：5.0.30
回答by Alex Martelli
The type of the field in a foreign key must be the same as the type of the column they're referencing. You have the following (snipping):
外键中字段的类型必须与它们引用的列的类型相同。您有以下内容（截图）：
CREATE TABLE IF NOT EXISTS `state` (
`state_id` INT NOT NULL AUTO_INCREMENT ,
...
CREATE TABLE IF NOT EXISTS `brand` (
`brand_id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
...
CREATE TABLE IF NOT EXISTS `location` (
...
`state_id` TINYINT UNSIGNED NULL DEFAULT NULL ,
...
`brand_id` TINYINT UNSIGNED NOT NULL ,
so you're trying to refer to INTfields (in tables stateand brand) with TINYINTfields in table location. I think that's the error it's complaining about. Not sure how it came up in the first place, or why zeroing out FOREIGN_KEY_CHECKSdoesn't stop MySQL from diagnosing the error, but what happens if you fix this type mismatch?
所以你要参考INT的字段（表中state和brand）与TINYINT表中的字段location。我认为这就是它抱怨的错误。不确定它最初是如何出现的，或者为什么清零FOREIGN_KEY_CHECKS不会阻止 MySQL 诊断错误，但是如果修复这种类型不匹配会发生什么？
回答by juacala
For others looking at this thread, there are a LOT of reasons that you can get this error:
对于查看此线程的其他人，您可能会收到此错误的原因有很多：
See the following link for a complete list for errno 150, errno 121 and other MySQL Foreign Key Errors:
有关 errno 150、errno 121 和其他 MySQL 外键错误的完整列表，请参阅以下链接：
回答by juanmf
just checked @juacala 's answer. But didn't help me. Here's the message I sent @ELIACOM when I found my source of trouble.:
刚刚检查了@juacala 的答案。但没有帮助我。这是我找到问题根源后发送给@ELIACOM 的消息：
I was reading this great source of solutions, but my issue is not adressed. I was trying to add an FK in a INNODB table pointing to a PK in a MyISAM. After setting the MyIsam to INNODB it worked. I checked your whole list before realizing that. Cheers.
我正在阅读这个很好的解决方案来源，但我的问题没有得到解决。我试图在 INNODB 表中添加一个 FK 指向 MyISAM 中的一个 PK。将 MyIsam 设置为 INNODB 后，它起作用了。在意识到这一点之前，我检查了你的整个清单。干杯。
回答by M?rt Rang
I had same issue. Somehow I didn't notice to set auto_increment value to the table id. Maybe it helps someone.
我有同样的问题。不知何故，我没有注意到将 auto_increment 值设置为表 id。也许它可以帮助某人。
