MySQL Creating tables with Foreign Keys giving errno 150

Question

I am trying to create a table in MySQL with two foreign keys  which reference the primary keys in 2 other tables  but I am getting an errno  150 error and it will not create the table   Here is the SQL for all 3 tables   CREATE TABLE role groups      role group id  int 11  NOT NULL  AUTO INCREMENT      name  varchar 20      description  varchar 200     PRIMARY KEY   role group id     ENGINE InnoDB   CREATE TABLE IF NOT EXISTS  roles       role id  int 11  NOT NULL AUTO INCREMENT     name  varchar 50      description  varchar 200     PRIMARY KEY   role id     ENGINE InnoDB   create table role map      role map id  int not null  auto increment      role id  int not null     role group id  int not null    primary key  role map id      foreign key  role id   references roles  role id      foreign key  role group id   references role groups  role group id     engine InnoDB    Any help would be greatly appreciated

User · Answer

I had the same problem with ALTER TABLE ADD FOREIGN KEY   After an hour  I found that these conditions must be satisfied to not get error 150    The Parent table must exist before you define a foreign key to reference it  You must define the tables in the right order  Parent table first  then the Child table  If both tables references each other  you must create one table without FK constraints  then create the second table  then add the FK constraint to the first table with ALTER TABLE   The two tables must both support foreign key constraints  i e  ENGINE InnoDB  Other storage engines silently ignore foreign key definitions  so they return no error or warning  but the FK constraint is not saved  The referenced columns in the Parent table must be the left-most columns of a key  Best if the key in the Parent is PRIMARY KEY or UNIQUE KEY  The FK definition must reference the PK column s  in the same order as the PK definition  For example  if the FK REFERENCES Parent a b c  then the Parent s PK must not be defined on columns in order  a c b   The PK column s  in the Parent table must be the same data type as the FK column s  in the Child table  For example  if a PK column in the Parent table is UNSIGNED  be sure to define UNSIGNED for the corresponding column in the Child table field    Exception  length of strings may be different  For example  VARCHAR 10  can reference VARCHAR 20  or vice versa  Any string-type FK column s  must have the same character set and collation as the corresponding PK column s   If there is data already in the Child table  every value in the FK column s  must match a value in the Parent table PK column s   Check this with a query like   SELECT COUNT    FROM Child LEFT OUTER JOIN Parent ON Child FK   Parent PK  WHERE Parent PK IS NULL    This must return zero  0  unmatched values  Obviously  this query is an generic example  you must substitute your table names and column names  Neither the Parent table nor the Child table can be a TEMPORARY table  Neither the Parent table nor the Child table can be a PARTITIONED table  If you declare a FK with the ON DELETE SET NULL option  then the FK column s  must be nullable  If you declare a constraint name for a foreign key  the constraint name must be unique in the whole schema  not only in the table in which the constraint is defined  Two tables may not have their own constraint with the same name  If there are any other FK s in other tables pointing at the same field you are attempting to create the new FK for  and they are malformed  i e  different collation   they will need to be made consistent first   This may be a result of past changes where SET FOREIGN KEY CHECKS   0  was utilized with an inconsistent relationship defined by mistake  See  andrewdotn s answer below for instructions on how to identify these problem FK s    Hope this helps

User · Answer

I ve found another reason this fails    case sensitive table names   For this table definition  CREATE TABLE user     userId int PRIMARY KEY AUTO INCREMENT    username varchar 30  NOT NULL   ENGINE InnoDB    This table definition works  CREATE TABLE product     id int PRIMARY KEY AUTO INCREMENT    userId int    FOREIGN KEY fkProductUser1 userId  REFERENCES   u  ser userId    ENGINE InnoDB    whereas this one fails  CREATE TABLE product     id int PRIMARY KEY AUTO INCREMENT    userId int    FOREIGN KEY fkProductUser1 userId  REFERENCES User userId    ENGINE InnoDB    The fact that it worked on Windows and failed on Unix took me a couple of hours to figure out  Hope that helps someone else

User · Answer

For others that find this SO entry via Google  Be sure that you aren t trying to do a SET NULL action on a foreign key  to be  column defined as  NOT NULL   That caused great frustration until I remembered to do a CHECK ENGINE INNODB STATUS

User · Answer

Side notes too big for a Comment   There is no need for an AUTO INCREMENT id in a mapping table  get rid of it   Change the PRIMARY KEY to  role id  role group id   in either order    This will make accesses faster   Since you probably want to map both directions  also add an INDEX with those two columns in the opposite order    There is no need to make it UNIQUE    More tips   http   mysql rjweb org doc php index cookbook mysql speeding up wp postmeta

User · Answer

In my case it was due to the fact that the field that was a foreign key field had a too long name  ie  foreign key  some other table with long name id   Try sth shorter  Error message is a bit misleading in that case    Also  as  Jon mentioned earlier - field definitions have to be the same  watch out for unsigned subtype

User · Answer

Make sure that the properties of the two fields you are trying to link with a constraint are exactly the same   Often  the  unsigned  property on an ID column will catch you out   ALTER TABLE  dbname   tablename  CHANGE  fieldname   fieldname  int 10  UNSIGNED NULL

User · Answer

MySQL Workbench 6 3 for Mac OS  Problem  errno 150 on table X when trying to do Forward Engineering on a DB diagram  20 out of 21 succeeded  1 failed  If FKs on table X were deleted  the error moved to a different table that wasn t failing before  Changed all tables engine to myISAM and it worked just fine

User · Answer

When the foreign key constraint is based on varchar type  then in addition to the list provided by marv-el the target column must have an unique constraint

User · Answer

For people who are viewing this thread with the same problem   There are a lot of reasons for getting errors like this  For a fairly complete list of causes  and solutions of foreign key errors in MySQL  including those discussed here   check out this link   MySQL Foreign Key Errors and Errno 150

User · Answer

What s the current state of your database when you run this script   Is it completely empty   Your SQL runs fine for me when creating a database from scratch  but errno 150 usually has to do with dropping  amp  recreating tables that are part of a foreign key  I m getting the feeling you re not working with a 100  fresh and new database   If you re erroring out when  source -ing your SQL file  you should be able to run the command  SHOW ENGINE INNODB STATUS  from the MySQL prompt immediately after the  source  command to see more detailed error info   You may want to check out the manual entry too      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 right column names and types  and it must have indexes on the referenced keys  as stated earlier  If these are not satisfied  MySQL returns error number 1005 and refers to error 150 in the error message   If MySQL reports an error number 1005 from a CREATE TABLE statement  and the error message refers to error 150  table creation failed because a foreign key constraint was not correctly formed           MySQL 5 1 reference manual

User · Answer

Also worth checking that you aren t accidentally operating on the wrong database  This error will occur if the foreign table does not exist  Why does MySQL have to be so cryptic

User · Answer

Please make sure at first that   you are using InnoDB tables  field for FOREIGN KEY has the same type and length     as source field    I had the same trouble and I ve fixed it  I had unsigned INT for one field and just integer for other field

User · Answer

MySQL   s generic    errno 150    message    means that a foreign key constraint was not correctly formed     As you probably already know if you are reading this page  the generic    errno  150    error message is really unhelpful  However   You can get the actual error message by running SHOW ENGINE INNODB STATUS  and then looking for LATEST FOREIGN KEY ERROR in the output   For example  this attempt to create a foreign key constraint   CREATE TABLE t1  id INTEGER    CREATE TABLE t2  t1 id INTEGER   CONSTRAINT FOREIGN KEY  t1 id  REFERENCES t1  id      fails with the error Can t create table  test t2   errno  150   That doesn   t tell anyone anything useful other than that it   s a foreign key problem  But run SHOW ENGINE INNODB STATUS  and it will say   ------------------------ LATEST FOREIGN KEY ERROR ------------------------ 130811 23 36 38 Error in foreign key constraint of table test t2  FOREIGN KEY  t1 id  REFERENCES t1  id    Cannot find an index in the referenced table where the referenced columns appear as the first columns  or column types in the table and the referenced table do not match for constraint    It says that the problem is it can   t find an index  SHOW INDEX FROM t1 shows that there aren   t any indexes at all for table t1  Fix that by  say  defining a primary key on t1  and the foreign key constraint will be created successfully

User · Answer

execute below line before creating table      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

I encountered the same problem  but I check find that I hadn t the parent table  So I just edit the parent migration in front of the child migration  Just do it

User · Answer

Make sure that the foreign keys are not listed as unique in the parent  I had this same problem and I solved it by demarcating it as not unique

User · Answer

Helpful tip  use SHOW WARNINGS  after trying your CREATE query and you will receive the error as well as the more detailed warning       ---------------------------------------------------------------------------------------------------------    Level     Code   Message                                                                                                                                                                                                                                    --------- ------ --------------------------------------------------------------------------                          --------------------------------------------------------------------------------------------                          ---------------    Warning    150   Create table  fakeDatabase exampleTable  with foreign key constraint failed  There is no index in the referenced table where the referenced columns appear as the first columns      Error     1005   Can t create table  exampleTable   errno 150                                                                                                                                                                               --------- ------ --------------------------------------------------------------------------                          --------------------------------------------------------------------------------------------                          ---------------    So in this case  time to re-create my table

User · Answer

Definitely it is not the case but I found this mistake pretty common and unobvious  The target of a FOREIGN KEY could be not PRIMARY KEY  Te answer which become useful for me is   A FOREIGN KEY always must be pointed to a PRIMARY KEY true field of other table   CREATE TABLE users     id INT AUTO INCREMENT PRIMARY KEY     username VARCHAR 40     CREATE TABLE userroles     id INT AUTO INCREMENT PRIMARY KEY     user id INT NOT NULL     FOREIGN KEY user id  REFERENCES users id

User · Answer

As pointed by  andrewdotn the best way is to see the detailed error SHOW ENGINE INNODB STATUS   instead of just an error code   One of the reasons could be that an index already exists with the same name  may be in another table  As a practice  I recommend prefixing table name before the index name to avoid such collisions  e g  instead of idx userId use idx userActionMapping userId

User · Answer

This is usually happening when you try to source file into existing database  Drop all the tables first  or the DB itself    And then source file with SET foreign key checks   0  at the beginning and SET foreign key checks   1  at the end

[mysql] MySQL Creating tables with Foreign Keys giving errno: 150

Examples related to mysql

Examples related to foreign-keys

Examples related to mysql-error-150