MySQL: Can't create table (errno: 150)

I am trying to import a .sql file and its failing on creating tables.

Here's the query that fails:

CREATE TABLE `data` (
`id` int(10) unsigned NOT NULL,
`name` varchar(100) NOT NULL,
`value` varchar(15) NOT NULL,
UNIQUE KEY `id` (`id`,`name`),
CONSTRAINT `data_ibfk_1` FOREIGN KEY (`id`) REFERENCES `keywords` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;    

I exported the .sql from the the same database, I dropped all the tables and now im trying to import it, why is it failing?

MySQL: Can't create table './dbname/data.frm' (errno: 150)

 Answers

From the MySQL - FOREIGN KEY Constraints Documentation:

If you re-create a table that was dropped, it must have a definition that conforms to the foreign key constraints referencing it. It must have the correct column names and types, and it must have indexes on the referenced keys, as stated earlier. If these are not satisfied, MySQL returns Error 1005 and refers to Error 150 in the error message, which means that a foreign key constraint was not correctly formed. Similarly, if an ALTER TABLE fails due to Error 150, this means that a foreign key definition would be incorrectly formed for the altered table.

---

You can get the actual error message by running SHOW ENGINE INNODB STATUS; and then looking for LATEST FOREIGN KEY ERROR in the output.

Source: answer from another user in a similar question

---

I think all these answers while correct are misleading to the question.

The actual answer is this before you start a restore, if you're restoring a dump file with foreign keys:

SET FOREIGN_KEY_CHECKS=0;

because naturally the restore will be creating some constraints before the foreign table even exists.

---

Error no. 150 means a foreign key constraint failure. You are probably creating this table before the table the foreign key depends on (table keywords). Create that table first and it should work fine.

If it doesn't, remove the foreign key statement and add it after the table is created - you will get a more meaningful error message about the specific constraint failure.

---

Sometimes MySQL is just super stupid - i can understand the reason cause of foreign-keys.. but in my case, i have just dropped the whole database, and i still get the error... why? i mean, there is no database anymore... and the sql-user i'm using has no access to any other db's on the server... i mean, the server is "empty" for the current user and i still get this error? Sorry but i guess MySQL is lying to me... but i can deal with it :) Just add these two lines of SQL around your fucky statement:

SET FOREIGN_KEY_CHECKS = 0;
# some code that gives you errno: 150
SET FOREIGN_KEY_CHECKS = 1;

Now the sql should be executed... If you really have a foreign-key problem, it would show up to you by the line where you will enable the checks again - this will fail then.. but my server is just quiet :)

---

After cruising through the answers above, and experimenting a bit, this is an effective way to solve Foreign Key errors in MySQL (1005 - error 150).

For the foreign key to be properly created, all MySQL asks for is:

• All referenced keys MUST have either PRIMARY or UNIQUE index.
• Referencing Column again MUST have identical data type to the Referenced column.

Satisfy these requirements and all will be well.

---

If the PK table is created in one CHARSET and then you create FK table in another CHARSET..then also you might get this error...I too got this error but after changing the charset to PK charset then it got executed without errors

create table users
(
------------
-------------
)DEFAULT CHARSET=latin1;


create table Emp
(
---------
---------
---------
FOREIGN KEY (userid) REFERENCES users(id) on update cascade on delete cascade)ENGINE=InnoDB, DEFAULT CHARSET=latin1;

---

I experienced this error when have ported Windows application to Linux. In Windows, database table names are case-insensitive, and in Linux they are case-sensitive, probably because of file system difference. So, on Windows table Table1 is the same as table1, and in REFERENCES both table1 and Table1 works. On Linux, when application used table1 instead of Table1 when it created database structure I saw error #150; when I made correct character case in Table1 references, it started to work on Linux too. So, if nothing else helps, make you sure that in REFERENCES you use correct character case in table name when you on Linux.

---

Please make sure both your primary key column and referenced column have the same data types and attributes (unsigned, binary, unsigned zerofill etc).

---

I had the same error. In my case the reason for the error was that I had a ON DELETE SET NULL statement in the constraint while the field on which I put the constraint in its definition had a NOT NULL statement. Allowing NULL in the field solved the problem.

---

I had a similar problem but mine was because i was adding a new field to an existing table that had data , and the new field was referencing another field from the parent table and also had the Defination of NOT NULL and without any DEFAULT VALUES. - I found out the reason things were not working was because

1. My new field needed to autofill the blank fields with a value from the parent table on each record, before the constraint could be applied. Every time the constraint is applied it needs to leave the Integrity of the table data intact. Implementing the Constraint (Foreign Key) yet there were some database records that did not have the values from the parent table would mean the data is corrupt so MySQL would NEVER ENFORCE YOUR CONSTRAINT

It is important to remember that under normal circumstances if you planned your database well ahead of time, and implemented constraints before data insertion this particular scenario would be avoided

The easier Approach to avoid this gotcha is to

• Save your database tables data
• Truncate the table data (and table artifacts i.e indexes etc)
• Apply the Constraints
• Import Your Data

I Hope this helps someone

---

Make sure that the all tables can support foreign key - InnoDB engine

---

I had a similar problem when dumping a Django mysql database with a single table. I was able to fix the problem by dumping the database to a text file, moving the table in question to the end of the file using emacs and importing the modified sql dump file into the new instance.

HTH Uwe

---

I've corrected the problem by making the variable accept null

ALTER TABLE `ajout_norme` 
CHANGE `type_norme_code` `type_norme_code` VARCHAR( 2 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL

---

Create the table without foreign key, then set the foreign key separately.

Mysql: Error Code: 1005. Can't create table '…' (errno: 150)

I searched for a solution to this problem on internet and checked the SO questions but no solution worked for my case.

I want to create a foreign key from table sira_no to metal_kod.

ALTER TABLE sira_no 
    ADD CONSTRAINT METAL_KODU FOREIGN KEY(METAL_KODU) 
    REFERENCES metal_kod(METAL_KODU) 
    ON DELETE SET NULL 
    ON UPDATE SET NULL ;

This script returns:

Error Code: 1005. Can't create table 'ebs.#sql-f48_1a3' (errno: 150) 

I tried adding index to the referenced table:

CREATE INDEX METAL_KODU_INDEX ON metal_kod (METAL_KODU);

I checked METAL_KODU on both tables (charset and collation). But couldn't find a solution to this problem. Does anyone have any idea? Thanks in advance.

EDIT: Here is the metal_kod table:

METAL_KODU  varchar(4)  NO  PRI     
DURUM   bit(1)  NO          
METAL_ISMI  varchar(30) NO          
AYAR_YOGUNLUK   smallint(6) YES     100 

 Answers

Error Code: 1005 -- there is a wrong primary key reference in your code

usually it's due to a reference FK field not exist. might be you have typo mistake,or check case it should be same, or there's a field-type mismatch. FK-linked fields must match definitions exactly.

Some Known causes may be :

1. The two key fields type and/or size doesn’t match exactly. For example, if one is INT(10) the key field needs to be INT(10) as well and not INT(11) or TINYINT. You may want to confirm the field size using SHOW CREATE TABLE because Query Browser will sometimes visually show just INTEGER for both INT(10) and INT(11). You should also check that one is not SIGNED and the other is UNSIGNED. They both need to be exactly the same.
2. One of the key field that you are trying to reference does not have an index and/or is not a primary key. If one of the fields in the relationship is not a primary key, you must create an index for that field.
3. 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.
4. One or both of your tables is a MyISAM table. In order to use foreign keys, the tables must both be InnoDB. (Actually, if both tables are MyISAM then you won’t get an error message - it just won’t create the key.) In Query Browser, you can specify the table type.
5. You have specified a cascade ON DELETE SET NULL, but the relevant key field is set to NOT NULL. You can fix this by either changing your cascade or setting the field to allow NULL values.
6. Make sure that the Charset and Collate options are the same both at the table level as well as individual field level for the key columns.
7. You have a default value (ie default=0) on your foreign key column
8. One of the fields in the relationship is part of a combination (composite) key and does not have it’s own individual index. Even though the field has an index as part of the composite key, you must create a separate index for only that key field in order to use it in a constraint.
9. You have a syntax error in your ALTER statement or you have mistyped one of the field names in the relationship
10. The name of your foreign key exceeds the max length of 64 chars.

---

Very often it happens, when the foreign key and the reference key don't have same type or same length

---

Error Code: 1005

Hello, I am putting across this answer so that anybody facing similar issue like mine can benefit for this response. Trust me this can be overlooked) (this may have been already answered and if so please excuse me)

I had similar issue, so here are few things that I did try (not in any order except for the solution :) )

1. Changed the foreign key names(didn't work)
2. Reduced the foreign key length
3. Verified the datatypes (darn nothing wrong)
4. Check indexes
5. Check the collations (everything fine, darn again)
6. Truncated the table, of no good use
7. Dropped the table and re-created
8. Tried to see if any circular reference is being created --- all fine

9. Finally, I saw that i had two editors open. One that in PHPStorm (jetbrains) and the other MySQL workbench. It seems that the PHPStorm / SQL workbench creates some kind of edit lock. I closed PHPStorm just to check if locking was the case (it could have been the other way around). This solved my problem. Hope this helps someone having similar issue.

---

It happened in my case because the name of the table being referenced in the constraint declaration isn't correct (i forgot the upper case in the table name)

ALTER TABLE `Window` ADD CONSTRAINT `Windows_ibfk_1` FOREIGN KEY (`WallId`) REFERENCES `Wall` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

I hope that helps.

---

MyISAM has been just mentioned. Simply try adding ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ; at the end of a statement, assuming that your other tables were created with MyISAM.

CREATE TABLE IF NOT EXISTS `tablename` (
  `key` bigint(20) NOT NULL AUTO_INCREMENT,
  FOREIGN KEY `key` (`key`) REFERENCES `othertable`(`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

---

I had the very same error message. Finally I figured out I misspelled the name of the table in the command:

ALTER TABLE `users` ADD FOREIGN KEY (country_id) REFERENCES country (id);

versus

ALTER TABLE `users` ADD FOREIGN KEY (country_id) REFERENCES countries (id);

I wonder why on earth mysql cannot tell such a table does not exist...

MySQL: Creating table with FK error (errno 150)

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

 Answers

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?

---

just checked answer. But didn't help me. Here's the message 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.

MySQL: errorno 121

I'm getting this error in MySQL create. I'm doing:

CREATE TABLE `blogReply` (

    `Id`      INT(24)      NOT NULL AUTO_INCREMENT COMMENT 'Primary Key of This Table',
    `blogId`  INT(24)      NOT NULL COMMENT 'Blog where this reply was posted',
    `userId`  INT(24)      NULL COMMENT 'User the blog was posted by',
    `name`    VARCHAR(100) NULL DEFAULT 'Unknown' COMMENT 'The Name of the user that the reply was posted by',
    `email`   VARCHAR(100) NULL DEFAULT 'Unknown' COMMENT 'The Email of the user that the reply was posted by',
    `http`    VARCHAR(300) NULL DEFAULT 'Unknown' COMMENT 'The Webaddress of the user that the reply was posted by',
    `message` TEXT         NOT NULL COMMENT 'text of the blog',
    `votes`   INT(10)      DEFAULT 0 COMMENT 'Rating of the Blog',
    `ratedBy` TEXT         COMMENT 'People who have already Voted on this blog',
    `dateReg` BIGINT       NOT NULL COMMENT 'Date the User was Registered',

    PRIMARY KEY (`Id`),

    CONSTRAINT `FK_userId` FOREIGN KEY(`userId`)
        REFERENCES `user` (`Id`)
        ON DELETE SET NULL
        ON UPDATE CASCADE,

    CONSTRAINT `FK_blogId` FOREIGN KEY(`blogId`)
        REFERENCES `blog` (`Id`)
        ON DELETE CASCADE
        ON UPDATE CASCADE

) ENGINE = InnoDB;

Any Ideas? The Error States: Can't create table './xxxxxxxx/blogReply.frm' (errno: 121)

 Answers

Check that all your constraints are really spelled out correctly, also check that there's not any other tables that uses the constraint names FK_userId or FK_blogId

---

please check that your foreign key which you are creating is same in all aspects such as datatype with the referred table column . Each foreign key name should be unique for those tables in which it is created , it should not be used in anyother tables. for the above problem the Foreign key name "FK_userId" should not be used in any other table .

Mysql. Can't create table errno 150

I have to create a db with 2 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.

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.

 Answers

table1.field1 has no index defined on it.

It is required to place a FOREIGN KEY constraint on 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.

---

Depending on the version of MySQL you may need to create an index on table1.field1 first.

---

Another hint:

Even when your data types seem to be the same - in my case both columns had VARCHAR(50) - this is not enough.

You also need to make sure that both columns have the same COLLATION.

---

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.

---

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.

---

In my case, one table was using foreign key constraints on another table that didn't exist yet. This was happening due to a large makefile, so it wasn't as obvious as I would've expected.

---

I was using MySQL workBench. THe issue is you cannot use the same foreign key name, they need to be unique. So if more than one table will reference the same foreign key, each time there must be a unique name given.

---

In my case I got old table definition MyISAM in one of the tables and obviously I was unable to make foreign key to it from another table. Maybe this help someone.

So this may happen because of inconsistencies between two databases/fields definitions try to check:

Field Type
Field Collation
Table Engine

---

You also may encounter the same error when attempting to reference a composite key in your foreign key.

For example:

CREATE TABLE `article` (
  `id` int(10) unsigned NOT NULL,
  `type` enum('X','Y','Z') NOT NULL,
  PRIMARY KEY (`id`,`type`)
) ENGINE InnoDB;

CREATE TABLE `t1` (
  `user_id` int(10) unsigned NOT NULL,
  `type` enum('X','Y','Z') NOT NULL,
  `article_id` int(10) unsigned NOT NULL,
  CONSTRAINT `user_access_article_ibfk_2` FOREIGN KEY (`article_id`, `type`) REFERENCES `article` (`id`, `type`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB

In this case, it is important to use article_id and type field in the FK definition in the very same order as they appear in the article table PRIMARY KEY definition.

---

In very strange cases your database might be broken. In my case I did not have any foreign keys on the table and the only renaming the table or changing engine helped.

Turned out innoDB was broken, see: https://dba.stackexchange.com/questions/86853/1025-error-on-rename-of-table-errno-150-table-was-deleted-while-tried-to-a?lq=1

---

I got this error while trying to use a foreign key to reference a non-unique field. (which apparently is not allowed)