MySQL：用FK错误创建表（errno 150） [英] MySQL: Creating table with FK error (errno 150)
问题描述
我用 MySQL Workbench 创建了一个模型，现在正在尝试将它安装到一个mysql服务器。
$ b 使用文件>导出>正向工程师SQL CREATE Script ... 输出一个漂亮的大文件给我，所有的设置我寻求。我切换到 MySQL GUI工具（特定的查询浏览器）并加载（注意，我正在从一个官方MySQL工具到另一个）。然而，当我试图实际执行这个文件时，我得到了同样的错误$ / b>
SQLSTATE [HY000]：一般错误：1005
不能创建表$ b $ ./srs_dev/location.frm（errno：150）
OK，我对自己说，位置表有些问题。所以我检查输出文件中的定义。
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 TABLE IF EXISTS`state`;
CREATE TABLE不存在`state`（
`state_id` INT NOT NULL AUTO_INCREMENT，$ b $``iso_3166_2_code` VARCHAR（2）NOT NULL，
`name` VARCHAR（60）NOT NULL，
PRIMARY KEY（`state_id`））
ENGINE = InnoDB;
- ---------------------------------------- -------------
- 表'brand`
- --------------------- --------------------------------
DROP TABLE IF EXISTS`brand`;
CREATE TABLE如果不存在`brand`（
`brand_id` INT UNSIGNED NOT NULL AUTO_INCREMENT，$ b $````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;
- ---------------------------------------- -------------
- 表`location`
- --------------------- --------------------------------
DROP TABLE IF EXISTS`location`;
CREATE TABLE不存在`location`（
`location_id` INT UNSIGNED NOT NULL AUTO_INCREMENT，$ b $```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，
（20）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，$ b $``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
- 工作台：5.0.30
外键字段的类型必须与列的类型相同重新引用。你有以下（剪切）：
$ b $ pre $
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，
所以你要引用 INT 字段州和品牌）与 TINYINT 表 location 。我认为这是它所抱怨的错误。不知道它是如何出现的，或者为什么要清零 FOREIGN_KEY_CHECKS 不会停止MySQL诊断错误，但是如果你修复了这种类型的错误，会发生什么？
I've created a model with MySQL Workbench and am now attempting to install it to a mysql server.
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
SQLSTATE[HY000]: General error: 1005 Can't create table './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.
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
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 INT fields (in tables state and brand) with TINYINT fields 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_CHECKS doesn't stop MySQL from diagnosing the error, but what happens if you fix this type mismatch?
这篇关于MySQL：用FK错误创建表（errno 150）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
