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

User · Answer

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.

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  amp  vice versa

User · Answer

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

User · Answer

usually  the mismatch between foreign key  amp  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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

I faced this kind of issue while creating DB from the textfile    mysql -uroot -padmin  lt  E  important sampdb createdb sql mysql -uroot -padmin sampdb  lt  E  important sampdb create student sql mysql -uroot -padmin sampdb  lt  E  important sampdb create absence sql  mysql -uroot -padmin sampdb  lt  E  important sampdb insert student sql mysql -uroot -padmin sampdb  lt  E  important sampdb insert absence sql  mysql -uroot -padmin sampdb  lt  E  important sampdb load student sql mysql -uroot -padmin sampdb  lt  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

User · Answer

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

User · Answer

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

User · Answer

Change the engines of your tables  only innoDB supports foreign keys

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[mysql] MySQL: Can't create table (errno: 150)

Examples related to mysql

Examples related to sql

Examples related to phpmyadmin

Examples related to mysql-error-150