MySQL：创建带有外键的表时出现错误（错误号150）

Question

我使用MySQL Workbench创建了一个模型，现在想将其安装到mysql服务器上。通过使用文件>导出>正向工程SQL创建脚本...，它为我输出了一个很大的文件，其中包含我要求的所有设置。我切换到MySQL GUI Tools（具体来说是查询浏览器）并加载此脚本（请注意，我正在从一个官方的MySQL工具切换到另一个）。然而，当我尝试执行此文件时，我一遍又一遍地得到相同的错误信息。

SQLSTATE[HY000]: General error: 1005 Can't create table './srs_dev/location.frm' (errno: 150)

我对自己说："好吧"，表格位置有问题。所以我查看了输出文件中的定义。

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

我认为看起来没问题。我猜测可能是查询浏览器出了问题，所以我将这个文件放在服务器上并尝试以这种方式加载它。

] mysql -u admin -p -D dbname < path/to/create_file.sql

我也遇到了同样的错误。于是我开始谷歌这个问题，发现有各种各样的帐户都在谈论一个与InnoDB风格表相关的错误，它会在外键失败时失败，修复方法是将"SET FOREIGN_KEY_CHECKS=0;"添加到SQL脚本中。但是，正如您所看到的，这已经是MySQL Workbench生成的文件的一部分了。那么，我的问题是，我做了我认为应该做的事情，为什么这不起作用呢？

版本信息：

MySQL：5.0.45 GUI工具：1.2.17 Workbench：5.0.30

- Peter Bailey

---

虽然在你的SQL中可能不是这种情况，但同样的错误也可能是由于表之间没有唯一的外键名称引起的。 - user698426

Answer 1

外键字段的类型必须与它们引用的列的类型相同。您有以下情况（摘录）：

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 ,

所以你正在尝试使用表location中的TINYINT字段引用表state和brand中的INT字段。我认为这就是它抱怨的错误。不确定它最初是如何出现的，或者为什么将FOREIGN_KEY_CHECKS清零不能阻止MySQL诊断错误，但如果您解决此类型不匹配问题会发生什么？

- Alex Martelli

---

好的，嗯...哈哈。哇，这很尴尬。我一直在忙着寻找实际关系中的问题，却没有检查列本身。感谢你对此的第二次帮助！ - Peter Bailey

哇！当混合使用“INT”和“INT UNSIGNED”时，它也会发生。是的，这是我的错；但是，MySQL Workbench需要有关此事的警告！ - Mauro

Answer 2

对于其他查看此线程的人，有很多原因会导致您出现此错误：

请参阅以下链接，获取errno 150、errno 121和其他MySQL外键错误的完整列表：

MySQL Foreign Key Errors and Errno 150

- juacala

---

是的，谢谢！顺便说一句，我喜欢xkcd漫画。我非常能够理解！干杯。 - SecondSun24

这就是我需要的答案。我的问题是使用了MyISAM而不是InnoDB。 - Ryan

Answer 3

刚刚查看了@juacala的回答，但没有帮到我。这是我在找到问题源头时发送给@ELIACOM的消息：

我正在阅读这个很棒的解决方案来源，但我的问题没有得到解决。我试图在一个INNODB表中添加指向MyISAM中PK的FK。将MyIsam设置为INNODB后，它可以工作。在意识到这一点之前，我检查了你的整个列表。干杯。

- juanmf

---

这个已经在里面了，但是对于某些版本来说，外键添加会悄无声息地失败。它在“其他外键错误”列表下排名第二。 - juacala

Answer 4

我曾经遇到过同样的问题。不知何故，我没有注意将自动递增值设置为表ID。 也许对某些人有所帮助。

- Märt Rang

---

AUTO_INCREMENT与否对外键的有效性没有影响。 - ypercubeᵀᴹ