MySQL Error 1215: Cannot add foreign key constraint

Robert B Source

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
mysqlforeign-keys

Answers

answered 5 years ago Ike Walker #1

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.

answered 5 years ago Explosion Pills #2

Reasons you may get a foreign key constraint error:

  1. You are not using InnoDB as the engine on all tables.
  2. 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)
  3. The types of the columns are not the same (exception is the column on the referencing table can be nullable).
  4. If the PK/FK is a varchar make sure the collation is the same for both.

Update:

  1. 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.

answered 5 years ago Alex #3

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.

answered 5 years ago user2975399 #4

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.

answered 5 years ago user3478348 #5

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

answered 4 years ago CodeMed #6

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.

answered 4 years ago blueFroggy #7

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

answered 4 years ago arvind #8

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.

answered 4 years ago gathila #9

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
)

answered 4 years ago Domi #10

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!

answered 4 years ago nmgeek #11

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.

answered 4 years ago b5bus #12

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

answered 3 years ago Yacine Richthofen #13

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

answered 3 years ago Chris Neve #14

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.

answered 3 years ago Mozaffar #15

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.

answered 3 years ago Olivier Faucheux #16

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

answered 3 years ago Chad #17

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

answered 3 years ago Stephen Nortje #18

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

answered 2 years ago Dima Dz #19

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.

answered 2 years ago bortunac #20

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;

answered 2 years ago Dennis #21

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

answered 2 years ago robsch #22

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

answered 1 year ago MAHDI ABDULSAHIB #23

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->increments('id');
        $table->string('name');
        $table->TinyInteger('color_id')->unsigned();
        $table->foreign('color_id')->references('id')->on('colors');
        $table->timestamps();
    });
}

colors table

    public function up()
{
    Schema::create('flights', function (Blueprint $table) {
        $table->increments('id');
        $table->string('color');
        $table->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->tinyIncrements('id');


When you use primary key $table->Increments('id');

you should use Integer as a foreign key

    $table-> unsignedInteger('fk_id');
    $table->foreign('fk_id')->references('id')->on('table_name');

When you use primary key $table->tinyIncrements('id');

you should use unsignedTinyInteger as a foreign key

    $table-> unsignedTinyInteger('fk_id');
    $table->foreign('fk_id')->references('id')->on('table_name');

When you use primary key $table->smallIncrements('id');

you should use unsignedSmallInteger as a foreign key

    $table-> unsignedSmallInteger('fk_id');
    $table->foreign('fk_id')->references('id')->on('table_name');

When you use primary key $table->mediumIncrements('id');

you should use unsignedMediumInteger as a foreign key

    $table-> unsignedMediumInteger('fk_id');
    $table->foreign('fk_id')->references('id')->on('table_name');

answered 1 year ago noowie #24

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.

answered 12 months ago ajon #25

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.

answered 9 months ago Carlos Laspina #26

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.

answered 2 weeks ago Arvind Bhardwaj #27

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

SET FOREIGN_KEY_CHECKS=0;

comments powered by Disqus