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

Question

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

I've found that the columns must be identical (even the unsigned flag must match). — Justin Skiles, Dec 19 '12 at 19:54
For essentially all the causes of this error, here is an exhaustive resource for what causes errno 150 (and errno 121/other foreign key errors) in MySQL. — John Smith, Sep 29 '12 at 00:47
I suggest reading this blog post that lists 10 possible causes: http://verysimple.com/2006/10/22/mysql-error-number-1005-cant-create-table-mydbsql-328_45frm-errno-150/ — Mark Amery, Jan 02 '14 at 13:54
@CharlesWood: "_[John Smith](http://stackoverflow.com/users/1512162/john-smith)... seen Apr 6 '13 at 19:29_", that is about three months before your comment. I have a fear, that a mystery of "where" won't be revealed until the end of this dully world! :> — trejder, Nov 07 '14 at 13:17
Related: [MySQL Creating tables with Foreign Keys giving errno: 150](http://stackoverflow.com/questions/1457305/mysql-creating-tables-with-foreign-keys-giving-errno-150) — Denilson Sá Maia, Apr 18 '15 at 02:17
Possible duplicate of [MySQL Creating tables with Foreign Keys giving errno: 150](http://stackoverflow.com/questions/1457305/mysql-creating-tables-with-foreign-keys-giving-errno-150) — vog, Mar 17 '17 at 08:43

score 176 · Accepted Answer · edited Sep 22 '15 at 13:36

176

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.

edited Sep 22 '15 at 13:36

Taylan

3,045
3
28
38

answered Oct 31 '10 at 00:39

OMG Ponies

325,700
82
523
502

1

Can two columns from one table reference one column from another table, where it is PK? – Eugene Nov 05 '11 at 00:16
1

@Eugene: Each of the two columns can have a foreign key relationship to the PK in another table -- not both columns as a single foreign key relationship. – OMG Ponies Nov 05 '11 at 01:04
1

@OMGPonies: Thanks for answering this Question!..I was looking for it...I also asked a question here http://stackoverflow.com/questions/13487010/how-to-update-same-table-on-deletion-in-mysql ....Although I have some good answers but I want to be conform `Whether its possible to write Nested Query for my problem`? ..I would request you to please answer me too! – Grijesh Chauhan Nov 21 '12 at 15:32
21

My error was master table had MyISAM and child table InnoDB engine. Current create.sql script was using InnoDB for all tables, but I had very very old installation where first script used MyISAM. – Whome Apr 08 '13 at 09:50
2

@Whome - Yep, ran into the same issue here. – aroth Jun 28 '13 at 05:33
In our case, a big chunk of db migration scripts, we have to adjust the table creation order so that the table which owns the foreign key is created first, then the table which uses it. – ryenus Aug 14 '13 at 05:54
Given that the OP dropped *all* his tables before trying to import the SQL file, the precise issue you've just quoted obviously does not apply - there can't possibly be an existing table with a dangling foreign key pointing to the table being created, because there aren't any existing tables at all. – Mark Amery Jan 02 '14 at 13:44
You might also need to look at the collation of the tables involved, and perhaps the fields involved as well. They both need to use same collation. – Jimmy Ilenloa Feb 19 '15 at 10:57
So lets say that I created a table with a bad foreign key in the past, I want to drop the table and then re-create it with the good foreign key, how can I do that? – in need of help Jun 15 '15 at 14:08
@inneedofhelp You'll have to open a new question (after searching). – Mark Oct 22 '15 at 09:50
Had the same error as @Whome just changed the MyISAM engine to INNODB with the following command: **ALTER TABLE old_table ENGINE=INNODB;** – juan_carlos_yl Feb 10 '20 at 16:44
For me it was because I put `ON DELETE SET NULL` when the column did not allow it. – DrSAS Mar 06 '21 at 18:48

score 103 · Answer 2 · answered Oct 31 '10 at 00:28

103

Error 150 means you have a problem with your foreign key. Possibly the key on the foreign table isn't the exact same type?

answered Oct 31 '10 at 00:28

Dan McGrath

41,220
11
99
130

16

Thanks :) for me, the data types are INT but one unsigned while the other is not – Anh Nguyen Mar 04 '14 at 09:20
6

I often run across `BIGINT` vs `INT` when using schema generators. – Xeoncross Aug 29 '14 at 19:20
I ran into the same issue when foreign key is not INT value. The column must be UNIQUE when foreign key refers to it. – PhatHV Sep 21 '15 at 09:23
Same error if you do not have an index on the referenced column (and referenced column must appear first in the index) according to [the documentation](https://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html). – Stack Underflow Apr 25 '21 at 01:04

score 71 · Answer 3 · answered Jul 23 '14 at 17:28

71

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

answered Jul 23 '14 at 17:28

Denilson Sá Maia

47,466
33
109
111

8

This is actually very useful. It tells you the exact error. – Csongor Fagyal Sep 04 '14 at 23:34
thanks. It's a shame MySQL Workbench doesn't make use of this. – scipilot Nov 05 '14 at 23:38
Awesome. This is so helpful. Tells you the exact error. Mine was, to have have made the Column NULLABLE but had set "on delete set null". Thank you so much. – Abhishek Saini Jul 01 '16 at 12:09
If you have privileges on your server :( – Christopher Smit May 15 '20 at 11:49

score 31 · Answer 4 · answered Dec 09 '10 at 13:46

31

Data types must match exactly. If you are dealing with varchar types, the tables must use the same collation.

answered Dec 09 '10 at 13:46

Esben Skov Pedersen

4,437
2
32
46

4

Thanks for the collation bit. – arahant May 25 '14 at 18:00

score 29 · Answer 5 · edited Sep 06 '14 at 22:39

29

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.

edited Sep 06 '14 at 22:39

Sebas

21,192
9
55
109

answered Apr 25 '13 at 12:14

colin

401
4
4

Doing this didn't work for me, still gives the error. Any ideas? – Joseph Astrahan Jan 19 '19 at 06:39
I was trying to replay a backup one command at a time and this did the trick. – Brett Sutton Jan 14 '22 at 13:02

score 24 · Answer 6 · answered Aug 03 '11 at 11:53

24

In some cases, you may encounter this error message if there are different engines between the relating tables. For example, a table may be using InnoDB while the other uses MyISAM. Both need to be same

answered Aug 03 '11 at 11:53

pi.

1,441
3
19
25

Thanks - this was my problem. – scipilot Nov 05 '14 at 23:37
That was my issue. Thanks – thed0ctor Dec 03 '14 at 01:54
This can happen if you created a sql file of innodb table using mysqldump and they were exported as myisam talbes instead. – Amado Martinez Jan 03 '17 at 22:51

score 12 · Answer 7 · answered Oct 31 '10 at 00:29

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.

score 10 · Answer 8 · answered Mar 18 '15 at 17:21

There are quite a few things that can cause errno 150, so for people searching this topic, here is what I think is a close to exhaustive list (source Causes of Errno 150):

For errno 150 or errno 121, simply typing in SHOW ENGINE INNODB STATUS, there is a section called "LATEST FOREIGN KEY ERROR". Under that it will give you a very helpful error message, which typically will tell you right away what is the matter. You need SUPER privileges to run it, so if you don't have that, you'll just have to test out the following scenarios.

1) Data Types Don't Match: The types of the columns have to be the same

2) Parent Columns Not Indexed (Or Indexed in Wrong Order)

3) Column Collations Don't Match

4) Using SET NULL on a NOT NULL Column

5) Table Collations Don't Match: even if the column collations match, on some MySQL versions this can be a problem.

6) Parent Column Doesn't Actually Exist In Parent Table. Check spelling (and perhaps a space at the beginning or end of column)

7) One of the indexes on one of the columns is incomplete, or the column is too long for a complete index. Note that MySQL (unless you tweak it) has a maximum single column key length of 767 bytes (this corresponds to a varchar(255) UTF column)

In case you get an errno 121, here are a couple of causes:

1) The constraint name you chose is already taken

2) On some systems if there is a case difference in your statement and table names. This can bite you if you go from one server to another that have different case handling rules.

In some versions you get an errno 150 if the table isn't innodb, but in some versions it just fails silently. — juacala, Apr 14 '15 at 14:54
Thanks, this was great: | ------------------------ | LATEST FOREIGN KEY ERROR | ------------------------ | You have defined a SET NULL condition though some of the | columns are defined as NOT NULL. — Sam Critchley, Jan 24 '18 at 10:53

score 9 · Answer 9 · answered Jul 18 '13 at 16:08

9

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

answered Jul 18 '13 at 16:08

jebbie

1,418
3
17
27

This might cause problems if there are actually differences between the column and the column it is referencing. For instance. Say the referenced column is a varchar(200) and the referrer is varchar(50), then when a cascade is attempted weird behavior might ensue. I haven't run into an issue where errno 150 is issued because of data mismatch. – juacala Mar 18 '15 at 17:39
Interesting insight @juacala :) Funny to me is only, whenever i ran into this, my approach always fixed it... until today at least :D But we never stop learning, right ;) – jebbie Mar 19 '15 at 15:35
This actually helped me with a script liquibase generated. The script ran flawlessly on MySQL > 5.5, but failed for version 5.1. – delbertooo Oct 20 '15 at 12:38

score 5 · Answer 10 · answered Nov 24 '17 at 12:04

5

usually, the mismatch between foreign key & primary key causes the error:150.

The foreign key must have the same datatype as the primary key. Also, if the primary key is unsigned then the foreign key must also be unsigned.

answered Nov 24 '17 at 12:04

Rakesh

706
6
10

score 5 · Answer 11 · answered Jul 10 '18 at 09:08

I had same issue. It was related to table's column Collation and Character Set. Make sure Character Set and Collation must be same for both columns on two tables. If you want to set a foreign key on that. Example- If you put foreign key on userID column of userImage table referencing userID column of users table.Then Collation must be same that is utf8_general_ci and Character set utf8 for both columns of tables. Generally when you create a table mysql takes these two configuration from server settings.

Why didnt I see this poist before!? I spent an hour figuring out the root cause. It was the charset in my case. Referenced and referencing tables must have same charset. — Boss Man, Sep 26 '18 at 18:09

score 4 · Answer 12 · answered Jul 03 '13 at 14:50

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.

score 4 · Answer 13 · answered Apr 23 '14 at 14:34

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.

This was also my case! Moving the script from a case-insensitive (OS X) to a case-sensitive mysql version (Debian). — mircealungu, Aug 14 '18 at 11:47

score 3 · Answer 14 · answered Apr 17 '12 at 16:14

3

Change the engines of your tables, only innoDB supports foreign keys

answered Apr 17 '12 at 16:14

Lappies

903
2
11
29

score 3 · Answer 15 · answered Oct 16 '12 at 10:19

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;

score 3 · Answer 16 · edited Dec 23 '13 at 06:17

3

This error can occur if two tables have a reference, for example, one table is Student and another table is Education, and we want the Education table to have a foreign key reference of Student table. In this instance the column data type for both tables should be same, otherwise it will generate an error.

edited Dec 23 '13 at 06:17

Radix

667
5
28

answered Dec 23 '13 at 05:53

manzarul haque

56
1

score 3 · Answer 17 · answered Jun 16 '15 at 14:34

3

In most of the cases the problem is because of the ENGINE dIfference .If the parent is created by InnoDB then the referenced tables supposed to be created by MyISAM & vice versa

answered Jun 16 '15 at 14:34

Sunil Kumar Mohanty

31
2

score 3 · Answer 18 · answered Oct 29 '15 at 14:39

3

In my case. I had problems with engine and charset because my Hosting server change settings and my new tables was MyISAM but my old tables are InnoDB. Just i changed.

answered Oct 29 '15 at 14:39

Ignacio Hernández

327
3
9

This was right for me, because I was altering a database not made by me. – FonzTech Jun 13 '18 at 09:51

score 2 · Answer 19 · answered Sep 19 '13 at 05:43

2

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

answered Sep 19 '13 at 05:43

Basit

1,830
2
31
50

score 2 · Answer 20 · answered Nov 06 '14 at 12:45

A real edge case is where you have used an MySQL tool, (Sequel Pro in my case) to rename a database. Then created a database with the same name.

This kept foreign key constraints to the same database name, so the renamed database (e.g. my_db_renamed) had foreign key constraints in the newly created database (my_db)

Not sure if this is a bug in Sequel Pro, or if some use case requires this behaviour, but it cost me best part of a morning :/

score 2 · Answer 21 · answered Nov 22 '14 at 18:59

2

The column of PARENT table to which you are referring to from child table has to be unique. If it is not, cause an error no 150.

answered Nov 22 '14 at 18:59

Dila Ram Gurung

21
1

it would probably be worth you adding in a bit more detail - e.g the specific column and table names – Jonathan Nov 22 '14 at 19:12

score 2 · Answer 22 · answered Jan 06 '15 at 12:51

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.

score 2 · Answer 23 · answered Jan 14 '16 at 13:09

I faced this kind of issue while creating DB from the textfile.

mysql -uroot -padmin < E:\important\sampdb\createdb.sql
mysql -uroot -padmin sampdb < E:\important\sampdb\create_student.sql
mysql -uroot -padmin sampdb < E:\important\sampdb\create_absence.sql

mysql -uroot -padmin sampdb < E:\important\sampdb\insert_student.sql
mysql -uroot -padmin sampdb < E:\important\sampdb\insert_absence.sql

mysql -uroot -padmin sampdb < E:\important\sampdb\load_student.sql
mysql -uroot -padmin sampdb < E:\important\sampdb\load_absence.sql

I just wrote the above lines in Create.batand run the bat file.

My mistake is in the sequence order of execution in my sql files. I tried to create table with primary key and also foreign key. While its running it will search for the reference table but tables are not there. So it will return those kind of error.

If you creating tables with foreign key then check the reference tables were present or not. And also check the name of the reference tables and fields.

In other words, you tried to create a table with a foreign key pointing to another table which didn't exist yet. Create the tables in the correct order to solve the problem. — Vincent, Mar 25 '19 at 23:19

score 2 · Answer 24 · answered Oct 24 '16 at 11:04

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

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

score 2 · Answer 25 · answered Nov 17 '17 at 10:02

2

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

answered Nov 17 '17 at 10:02

stuckhelper

71
6

score 1 · Answer 26 · answered Oct 02 '12 at 05:16

1

Perhaps this will help? The definition of the primary key column should be exactly the same as the foreign key column.

answered Oct 02 '12 at 05:16

Mukus

4,870
2
43
56

score 1 · Answer 27 · answered Mar 21 '13 at 15:22

1

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

answered Mar 21 '13 at 15:22

joksy82

405
2
6
13

score 1 · Answer 28 · answered Jul 16 '15 at 16:07

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

score 1 · Answer 29 · edited Apr 14 '16 at 16:44

1

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

edited Apr 14 '16 at 16:44

Ahmad

5,551
8
41
57

answered Apr 14 '16 at 15:50

Fahmi

11
1

score 1 · Answer 30 · answered Oct 25 '17 at 05:03

I got the same problem when executing a series of MySQL commands. Mine occurs during creating a table when referencing a foreign key to other table which was not created yet. It's the sequence of table existence before referencing.

The solution: Create the parent tables first before creating a child table which has a foreign key.

score 1 · Answer 31 · answered Nov 28 '17 at 11:51

I had same error, then i have created referenced table first and then referred table

for example if you have employee and department tables your assigning foreign constraint on dept_no in employee table then make sure that the department table is created and have assigned primary key constraints to dept_no.

this worked for me...

score 0 · Answer 32 · edited Jul 21 '18 at 19:54

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.

score 0 · Answer 33 · answered Sep 06 '19 at 11:43

execute below line:
  SET FOREIGN_KEY_CHECKS = 0;


FOREIGN_KEY_CHECKS option specifies whether or not to check foreign key constraints for InnoDB tables. 

-- Specify to check foreign key constraints (this is the default)

    SET FOREIGN_KEY_CHECKS = 1;
 

-- Do not check foreign key constraints

   SET FOREIGN_KEY_CHECKS = 0;


When to Use :
Temporarily disabling referential constraints (set FOREIGN_KEY_CHECKS to 0) is useful when you need to re-create the tables and load data in any parent-child order.

score 0 · Answer 34 · answered Jan 20 '22 at 12:55

0

It's a silly mistake but I got 'errno: 150' because I had forgotten to define the parent property that was being referenced as a primary key.

Just sharing in case anyone else has the same issue.

answered Jan 20 '22 at 12:55

Josh McGee

443
6
16

score -7 · Answer 35 · edited Aug 08 '12 at 08:33

-7

Try:

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;

You need to put a "," between CONSTRAINT and FOREIGN.

edited Aug 08 '12 at 08:33

j0k

22,600
28
79
90

answered Aug 07 '12 at 18:43

Leandro Leal

1

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

35 Answers35

Linked

Related