MySQL: Creating table with FK error (errno 150)

Question

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

Rutan

Although not the case in your SQL, the same error is also caused by not having unique foreign key names between tables. – Dennett

Answer 1

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?

Cushat

OK, um... haha. Wow, that's embarassing. I was so busy looking for something wrong with the actual relationship that I didn't check the columns themselves. Thanks for the 2nd pair of eyes on this! – Rutan

Wow! It happened also when mixing "INT" and "INT UNSIGNED". Yeah, this was my fault; but, MySQL Workbench needs a warning about this! – Gargantuan

Answer 2

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:

MySQL Foreign Key Errors and Errno 150

Manolo

Yeah, thank you! BTW, love the xkcd comic. I can relate in a big way! Cheers. – Altocumulus

This was the answer that I needed. My problem was MyISAM instead of InnoDB. – Epley

Answer 3

just checked @juacala 's answer. But didn't help me. Here's the message I sent @ELIACOM when I found my source of trouble.:

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.

Dowager

That's in there, but for some versions, the foreign key addition silently fails. It's #2 in the list under "other foreign key errors" – Manolo

Answer 4

I had same issue. Somehow I didn't notice to set auto_increment value to the table id. Maybe it helps someone.

Orthographize

AUTO_INCREMENT or not, has no effect on Foreign Key validity. – Preoccupy