MySQL Error 1215 Cannot add foreign key constraint

Question

I am trying to forward engineer my new schema onto my db server  but I can t figure out why I am getting this error   I ve tried to search for the answer here  but everything I ve found has said to either set the db engine to Innodb or to make sure the keys I m trying to use as a foreign key are primary keys in their own tables   I have done both of these things  if I m not mistaken   Any other help you guys could offer   Executing SQL script in server  ERROR  Error 1215  Cannot add foreign key constraint  -- ----------------------------------------------------- -- Table  Alternative Pathways   Clients has Staff  -- -----------------------------------------------------  CREATE  TABLE IF NOT EXISTS  Alternative Pathways   Clients has Staff       Clients Case Number  INT NOT NULL      Staff Emp ID  INT NOT NULL     PRIMARY KEY   Clients Case Number    Staff Emp ID       INDEX  fk Clients has Staff Staff1 idx    Staff Emp ID  ASC      INDEX  fk Clients has Staff Clients idx    Clients Case Number  ASC      CONSTRAINT  fk Clients has Staff Clients      FOREIGN KEY   Clients Case Number        REFERENCES  Alternative Pathways   Clients    Case Number        ON DELETE NO ACTION     ON UPDATE NO ACTION    CONSTRAINT  fk Clients has Staff Staff1      FOREIGN KEY   Staff Emp ID        REFERENCES  Alternative Pathways   Staff    Emp ID        ON DELETE NO ACTION     ON UPDATE NO ACTION  ENGINE   InnoDB   SQL script execution finished  statements  7 succeeded  1 failed  Here is the SQL for the parent tables   CREATE  TABLE IF NOT EXISTS  Alternative Pathways   Clients       Case Number  INT NOT NULL      First Name  CHAR 10  NULL      Middle Name  CHAR 10  NULL      Last Name  CHAR 10  NULL      Address  CHAR 50  NULL      Phone Number  INT 10  NULL     PRIMARY KEY   Case Number     ENGINE   InnoDB  CREATE  TABLE IF NOT EXISTS  Alternative Pathways   Staff       Emp ID  INT NOT NULL      First Name  CHAR 10  NULL      Middle Name  CHAR 10  NULL      Last Name  CHAR 10  NULL     PRIMARY KEY   Emp ID     ENGINE   InnoDB

User · Answer

This is a subtle version of what has already been said, but in my instance, I had 2 databases (foo and bar). I created foo first and I didn't realize it referenced a foreign key in bar.baz (which wasn't created yet). When I tried to create bar.baz (without any foreign keys), I kept getting this error. After looking around for a while I found the foreign key in foo.

So, long story short, If you get this error, you may have a pre-existing foreign key to the table being created.

User · Answer

Even i had the same problem   And the fault was with the  unsigned  marker in the FK s table PK

User · Answer

Check for table compatibility   For example  if one table is MyISAM and the other one is InnoDB  you may have this issue

User · Answer

There is a pitfall I have experienced with  Error 1215  Cannot add foreign key constraint  when using Laravel 4  especially with JeffreyWay s Laravel 4 Generators   In Laravel 4  you can use JeffreyWay s Generators to generate migration files to create tables one-by-one  which means  each migration file generates one table  You have to be aware of the fact that each migration file is generated with a timestamp in the filename  which gives the files an order  The order of generation is also the order of migration operation when you fire the Artisan CLI command  php artisan migrate   So  if a file asks for a foreign key constraint referring to a key which will be  but not yet  generated in a latter file  the Error 1215 is fired  In such case  what you have to do is adjust the order of migration files generation  Generate new files in proper order  copy-in the content  then delete the disordered old files

User · Answer

For others same error may not always be due to column type mismatch  you can find out more information about a mysql foriegn key error by issuing command  SHOW ENGINE INNODB STATUS    you may find a error near the top of the printed message something like     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

User · Answer

I had the same problem  I solved it doing this   I created the following line in the primary key   id int 11  unsigned NOT NULL AUTO INCREMENT   I found out this solution after trying to import a table in my schema builder  If it works for you  let me know    Good luck   Felipe T  rcio

User · Answer

For MySQL  INNODB      get definitions for columns you want to link  SELECT   FROM information schema columns WHERE  TABLE NAME IN  tb name   referenced table name   AND  COLUMN NAME  IN   col name   referenced col name   G   compare and verify both column definitions have      same COLUMN TYPE length   same COLATION    could be helpfull to play like  set foreign key checks 0  ALTER TABLE tb name ADD FOREIGN KEY col name  REFERENCES ref table ref column  ON DELETE     set foreign key checks 1

User · Answer

i had the same issue  my solution   Before   CREATE TABLE EMPRES   NoFilm smallint NOT NULL    PRIMARY KEY  NoFilm     FOREIGN KEY  NoFilm  REFERENCES cassettes       Solution   CREATE TABLE EMPRES  NoFilm smallint NOT NULL REFERENCES cassettes    PRIMARY KEY  NoFilm        I hope it s help

User · Answer

Be aware of the use of backquotes too  I had in a script the following statement  ALTER TABLE service ADD FOREIGN KEY  create by  REFERENCES  system user id      but the backquotes at the end were false  It should have been   ALTER TABLE service ADD FOREIGN KEY  create by  REFERENCES  system user   id      MySQL give unfortunalty no details on this error

User · Answer

I had the same error once  I just simply restarted the MySQL server and fixed the problem

User · Answer

when try to make foreign key when using laravel migration  like this example   user table      public function up         Schema  create  flights   function  Blueprint  table             table- gt increments  id             table- gt string  name             table- gt TinyInteger  color id  - gt unsigned             table- gt foreign  color id  - gt references  id  - gt on  colors             table- gt timestamps                colors table      public function up         Schema  create  flights   function  Blueprint  table             table- gt increments  id             table- gt string  color             table- gt timestamps                sometimes properties didn t work    PDOException  SQLSTATE HY000   General error  1215 Cannot add foreign key constraint   this error happened because the foreign key  type  in  user table    is deferent from primary key  type  in  colors table   To solve this problem should change the primary key in  colors table     table- gt tinyIncrements  id       When you use primary key  table- gt Increments  id     you should use Integer as a foreign key       table- gt  unsignedInteger  fk id         table- gt foreign  fk id  - gt references  id  - gt on  table name        When you use primary key  table- gt tinyIncrements  id     you should use unsignedTinyInteger as a foreign key       table- gt  unsignedTinyInteger  fk id         table- gt foreign  fk id  - gt references  id  - gt on  table name        When you use primary key  table- gt smallIncrements  id     you should use unsignedSmallInteger as a foreign key       table- gt  unsignedSmallInteger  fk id         table- gt foreign  fk id  - gt references  id  - gt on  table name        When you use primary key  table- gt mediumIncrements  id     you should use unsignedMediumInteger as a foreign key       table- gt  unsignedMediumInteger  fk id         table- gt foreign  fk id  - gt references  id  - gt on  table name

User · Answer

Reasons you may get a foreign key constraint error   You are not using InnoDB as the engine on all tables  You are trying to reference a nonexistent key on the target table   Make sure it is a key on the other table  it can be a primary or unique key  or just a key  The types of the columns are not the same  exception is the column on the referencing table can be nullable even if it is not nullable on the referenced table   If the PK FK is a varchar make sure the collation is the same for both   Update   One of the reasons may also be that the column you are using for ON DELETE SET NULL is not defined to be null  So make sure that the column is set default null   Check these

User · Answer

I just wanted to add this case as well for VARCHAR foreign key relation  I spent the last week trying to figure this out in MySQL Workbench 8 0 and was finally able to fix the error   Short Answer  The character set and collation of the schema  the table  the column  the referencing table  the referencing column and any other tables that reference to the parent table have to match    Long Answer  I had an ENUM datatype in my table  I changed this to VARCHAR and I can get the values from a reference table so that I don t have to alter the parent table to add additional options  This foreign-key relationship seemed straightforward but I got 1215 error  arvind s answer and the following link suggested the use of    SHOW ENGINE INNODB STATUS    On using this command I got the following verbose description for the error with no additional helpful information     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     Note that the internal storage type of ENUM and SET changed in    tables created with    InnoDB-4 1 12  and such columns in old tables    cannot be referenced by such columns in new tables     Please refer to http   dev mysql com doc refman 8 0 en innodb-foreign-key-constraints html for correct foreign key definition    After which I used SET FOREIGN KEY CHECKS 0  as suggested by Arvind Bharadwaj and the link here   This gave the following error message      Error Code  1822  Failed to add the foreign key constraint  Missing   index for constraint   At this point  I  reverse engineer -ed the schema and I was able to make the foreign-key relationship in the EER diagram  On  forward engineer -ing  I got the following error      Error 1452  Cannot add or update a child row  a foreign key constraint   fails   When I  forward engineer -ed the EER diagram to a new schema  the SQL script ran without issues  On comparing the generated SQL from the attempts to forward engineer  I found that the difference was the character set and collation  The parent table  child table and the two columns had utf8mb4 character set and utf8mb4 0900 ai ci collation  however  another column in the parent table was referenced using CHARACTER SET   utf8   COLLATE   utf8 bin   to a different child table   For the entire schema  I changed the character set and collation for all the tables and all the columns to the following   CHARACTER SET   utf8mb4 COLLATE   utf8mb4 general ci    This finally solved my problem with 1215 error   Side Note  The collation utf8mb4 general ci works in MySQL Workbench 5 0 or later  Collation utf8mb4 0900 ai ci works just for MySQL Workbench 8 0 or higher  I believe one of the reasons I had issues with character set and collation is due to MySQL Workbench upgrade to 8 0 in between  Here is a link that talks more about this collation

User · Answer

I got the same error while trying to add an fk  In my case the problem was caused by the FK table s PK which was marked as unsigned

User · Answer

This also happens when the type of the columns is not the same   e g  if the column you are referring to is UNSIGNED INT and the column being referred is INT then you get this error

User · Answer

Error 1215 is an annoying one  Explosion Pill s answer covers the basics  You want to make sure to start from there  However  there are more  much more subtle cases to look out for   For example  when you try to link up PRIMARY KEYs of different tables  make sure to provide proper ON UPDATE and ON DELETE options  E g        PRIMARY KEY   id    FOREIGN KEY   id   REFERENCES  t    other id   ON DELETE SET NULL        won t fly  because PRIMARY KEYs  such as id  can t be NULL   I am sure  there are even more  similarly subtle issues when adding these sort of constraints  which is why when coming across constraint errors  always make sure that the constraints and their implications make sense in your current context  Good luck with your error 1215

User · Answer

So I tried all the fixes above and no luck  I may be missing the error in my tables -just could not find the cause and  I kept getting error 1215  So I used this fix   In my local environment in phpMyAdmin  I exported data from the table in question  I selected format CSV  While still in phpMyAdmin with the table selected  I selected  More- Options   Here I scrolled down to  Copy table to  database table   Select  Structure only   Rename the table something  maybe just add the word  copy  next to the current table name  Click  Go  This will create a new table  Export the new table and import it to the new or other server  I am also using phpMyAdmin here also  Once imported change the name of the table back to its original name  Select the new table  select import  For format select CSV  Uncheck  enable foreign key checks   Select  Go   So far all is working good   I posted my fix on my blog

User · Answer

In my case  I had deleted a table using SET FOREIGN KEY CHECKS 0  then SET FOREIGN KEY CHECKS 1 after   When I went to reload the table  I got error 1215   The problem was there was another table in the database that had a foreign key to the table I had deleted and was reloading   Part of the reloading process involved changing a data type for one of the fields  which made the foreign key from the other table invalid  thus triggering error 1215   I resolved the problem by dropping and then reloading the other table with the new data type for the involved field

User · Answer

Another reason  if you use ON DELETE SET NULL all columns that are used in the foreign key must allow null values  Someone else found this out in this question    From my understanding it wouldn t be a problem regarding data integrity  but it seems that MySQL just doesn t support this feature  in 5 7

User · Answer

I experienced this error for a completely different reason  I used MySQL Workbench 6 3 for creating my Data Model  awesome tool   I noticed that when the column order defined in the Foreign Key constraint definition does not fit the table column sequence this error is also generated   It took me about 4 hours of trying everything else but checking that   Now all is working well and I can get back to coding   -

User · Answer

For me it was the column types  BigINT    INT    But then it still didn t work    So I checked the engines  Make sure Table1   InnoDB and Table   InnoDB

User · Answer

I m guessing that Clients Case Number and or Staff Emp ID are not exactly the same data type as Clients has Staff Clients Case Number and Clients has Staff Staff Emp ID   Perhaps the columns in the parent tables are INT UNSIGNED   They need to be exactly the same data type in both tables

User · Answer

Another source of this error is that you have 2 or more same table names which have the same foreign key names  This sometimes happens to people who use modelling and design software  like Mysql Workbench  and later generate the script from the design

User · Answer

I know i am VERY late to the party but i want to put it out here so that it is listed   As well as all of the above advice for making sure that fields are identically defined  and table types also have the same collation  make sure that you don t make the rookie mistake of trying to link fields where data in the CHILD field is not already in the PARENT field  If you have data that is in the CHILD field that you have not already entered in to the PARENT field then that will cause this error  It s a shame that the error message is not a bit more helpful   If you are unsure  then backup the table that has the Foreign Key  delete all the data and then try to create the Foreign Key  If successful then you what to do   Good luck

User · Answer

Check the collation of table  using SHOW TABLE STATUS you can check information about the tables  including the collation   Both tables have to has the same collation   It s happened to me

User · Answer

I can not find this error  CREATE TABLE RATING    Riv Id INT 5   Mov Id INT 10  DEFAULT 0  Stars INT 5   Rating date DATE    PRIMARY KEY  Riv Id  Mov Id    FOREIGN KEY  Riv Id  REFERENCES REVIEWER Reviewer ID  ON DELETE SET NULL ON UPDATE CASCADE   FOREIGN KEY  Mov Id  REFERENCES MOVIE Movie ID  ON DELETE SET DEFAULT ON UPDATE CASCADE

User · Answer

For me  the 1215 error occurred when I was importing a dumpfile created by mysqldump  which creates the tables alphabetically  which in my case  caused foreign keys to reference tables created later in the file   Props to this page for pointing it out  https   www percona com blog 2017 04 06 dealing-mysql-error-code-1215-cannot-add-foreign-key-constraint    Since mysqldump orders tables alphabetically and I did not want to change the names of tables  I followed the instructions in the answer by JeremyWeir on this page  which states to put set FOREIGN KEY CHECKS   0  at the top of the dump file and put SET FOREIGN KEY CHECKS   1  at the bottom of the dump file   That solution worked for me

User · Answer

In my case I had to disable FOREIGN KEY checks as the source tables did not exist   SET FOREIGN KEY CHECKS 0

User · Answer

Wooo I just got it   It was a mix of a lot of already posted answers  innoDB  unsigned  etc   One thing I didn t see here though is   if your FK is pointing on a PK  ensure the source column has a value that makes sense  For example  if the PK is a mediumint 8   make sure the source column also contains a mediumint 8   That was part of the problem for me

User · Answer

When this error occurrs because the referenced table uses the MyISAM engine this answer provides a quick way to convert your database so all Django model tables use InnoDB   https   stackoverflow com a 15389961 2950621  It s a Django management command called convert to innodb

[mysql] MySQL Error 1215: Cannot add foreign key constraint

Examples related to mysql

Examples related to foreign-keys