sql – mysql Foreign key constraint is incorrectly formed error

The Question :

201 people think this question is useful

I have two tables, table1 is the parent table with a column ID and table2 with a column IDFromTable1 (not the actual name) when I put a FK on IDFromTable1 to ID in table1 I get the error Foreign key constraint is incorrectly formed error. I would like to delete table 2 record if table1 record gets deleted. Thanks for any help

ALTER TABLE `table2`  
   ADD CONSTRAINT `FK1` 
      FOREIGN KEY (`IDFromTable1`) REFERENCES `table1` (`ID`) 
      ON UPDATE CASCADE 
      ON DELETE CASCADE;

Let me know if any other information is needed. I am new to mysql

The Question Comments :
  • What engine are you using for your tables? What’s the type of table2.IDFromTable1and table1.ID?
  • Also, check that the character sets for both tables are the same.
  • Both tables engines are innoDB. not sure where to find character sets, and the are both are char type. The ID is the primary key in table1
  • Please provide table definitions for table1 and table2. How did you get this error? Do you use a tool to create the foreign key? It seems that it is not a MySQL native error.
  • @user516883 – Do you need help to get table definitions? In HeidiSQL you can simply click on the CREATE code tab.

The Answer 1

469 people think this answer is useful

I ran into this same problem with HeidiSQL. The error you receive is very cryptic. My problem ended up being that the foreign key column and the referencing column were not of the same type or length.

The foreign key column was SMALLINT(5) UNSIGNED and the referenced column was INT(10) UNSIGNED. Once I made them both the same exact type, the foreign key creation worked perfectly.

The Answer 2

56 people think this answer is useful

I had the same problem when the parent table was created using MyISAM engine. It’s a silly mistake, which I fixed with:

ALTER TABLE parent_table ENGINE=InnoDB;

The Answer 3

30 people think this answer is useful

make sure columns are identical(of same type) and if reference column is not primary_key, make sure it is INDEXED.

The Answer 4

21 people think this answer is useful

Syntax for defining foreign keys is very forgiving, but for anyone else tripping up on this, the fact that foreign keys must be “of the same type” applies even to collation, not just data type and length and bit signing.

Not that you’d mix collation in your model (would you?) but if you do, be sure your primary and foreign key fields are of the same collation type in phpmyadmin or Heidi SQL or whatever you use.

Hope this saves you the four hours of trial and error it cost me.

The Answer 5

14 people think this answer is useful

I had same problem, but solved it.

Just make sure that column ‘ID’ in ‘table1’ has UNIQUE index!

And of course the type, length of columns ‘ID’ and ‘IDFromTable1’ in these two tables has to be same. But you already know about this.

The Answer 6

12 people think this answer is useful

mysql error texts doesn’t help so much, in my case, the column had “not null” constraint, so the “on delete set null” was not allowed

The Answer 7

10 people think this answer is useful

Just for completion.

This error might be as well the case if you have a foreign key with VARCHAR(..) and the charset of the referenced table is different from the table referencing it.

e.g. VARCHAR(50) in a Latin1 Table is different than the VARCHAR(50) in a UTF8 Table.

The Answer 8

9 people think this answer is useful

if everything is ok, just add ->unsigned(); at the end of foregin key.

if it does not work, check the datatype of both fields. they must be the same.

The Answer 9

6 people think this answer is useful

One more probable cause for the display of this error. The order in which I was creating tables was wrong. I was trying to reference a key from a table that was not yet created.

The Answer 10

5 people think this answer is useful

(Last Resent) Even if the field name and data type is the same but the collation is not the same, it will also result to that problem.

For Example

    TBL NAME       |        DATA TYPE          |         COLLATION        

    ActivityID          |        INT                        |         latin1_general_ci     ActivityID          |        INT                        |         utf8_general_ci

Try Changing it into

    TBL NAME       |        DATA TYPE          |         COLLATION        

    ActivityID          |        INT                        |         latin1_general_ci     ActivityID          |        INT                        |         latin1_general_ci

….

This worked for me.

The Answer 11

5 people think this answer is useful

I had the same issue, both columns were INT(11) NOT NULL but I wan’t able to create the foreign key. I had to disable foreign keys checks to run it successfully :

SET FOREIGN_KEY_CHECKS=OFF;
ALTER TABLE ... ADD CONSTRAINT ...
SET FOREIGN_KEY_CHECKS=ON;

Hope this helps someone.

The Answer 12

3 people think this answer is useful

Although the other answers are quite helpful, just wanted to share my experience as well.

I faced the issue when I had deleted a table whose id was already being referenced as foreign key in other tables (with data) and tried to recreate/import the table with some additional columns.

The query for recreation (generated in phpMyAdmin) looked like the following:

CREATE TABLE `the_table` (
  `id` int(11) NOT NULL,            /* No PRIMARY KEY index */  
  `name` varchar(255) NOT NULL,
  `name_fa` varchar(255) NOT NULL,
  `name_pa` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

... /* SOME DATA DUMP OPERATION */

ALTER TABLE `the_table`
  ADD PRIMARY KEY (`id`), /* PRIMARY KEY INDEX */
  ADD UNIQUE KEY `uk_acu_donor_name` (`name`);

As you may notice, the PRIMARY KEY index was set after the creation (and insertion of data) which was causing the problem.

Solution

The solution was to add the PRIMARY KEY index on table definition query for the id which was being referenced as foreign key, while also removing it from the ALTER TABLE part where indexes were being set:

CREATE TABLE `the_table` (
  `id` int(11) NOT NULL PRIMARY KEY,            /* <<== PRIMARY KEY INDEX ON CREATION */  
  `name` varchar(255) NOT NULL,
  `name_fa` varchar(255) NOT NULL,
  `name_pa` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The Answer 13

3 people think this answer is useful

Check the tables engine, both tables have to be the same engine, that helped me so much.

The Answer 14

3 people think this answer is useful

For anyone facing this problem, just run SHOW ENGINE INNODB STATUS and see the LATEST FOREIGN KEY ERROR section for details.

The Answer 15

2 people think this answer is useful

Try running following:

show create table Parent

//and check if type for both tables are the same, like myISAM or innoDB, etc
//Other aspects to check with this error message: the columns used as foreign 
keys must be indexed, they must be of the same type 
(if i.e one is of type smallint(5) and the other of type smallint(6), 
it won't work), and, if they are integers, they should be unsigned.

//or check for charsets
show variables like "character_set_database";
show variables like "collation_database";

//edited: try something like this
ALTER TABLE table2
ADD CONSTRAINT fk_IdTable2
FOREIGN KEY (Table1_Id)
REFERENCES Table1(Table1_Id)
ON UPDATE CASCADE 
ON DELETE CASCADE;

The Answer 16

2 people think this answer is useful

I had the same problems.

The issue is the reference column is not a primary key.

Make it a primary key and problem is solved.

The Answer 17

2 people think this answer is useful

I lost for hours for that!

PK in one table was utf8 in other was utf8_unicode_ci!

The Answer 18

1 people think this answer is useful

I had the same issue with Symfony 2.8.

I didn’t get it at first, because there were no similar problems with int length of foreign keys etc.

Finally I had to do the following in the project folder. (A server restart didn’t help!)

app/console doctrine:cache:clear-metadata app/console doctrine:cache:clear-query app/console doctrine:cache:clear-result

The Answer 19

1 people think this answer is useful

thanks S Doerin:

“Just for completion. This error might be as well the case if you have a foreign key with VARCHAR(..) and the charset of the referenced table is different from the table referencing it. e.g. VARCHAR(50) in a Latin1 Table is different than the VARCHAR(50) in a UTF8 Table.”

i solved this problem, changing the type of characters of the table. the creation have latin1 and the correct is utf8.

add the next line. DEFAULT CHARACTER SET = utf8;

The Answer 20

1 people think this answer is useful

I had issues using Alter table to add a foreign key between two tables and the thing that helped me was making sure each column that I was trying to add a foreign key relationship to was indexed. To do this in PHP myAdmin: Go to the table and click on the structure tab. Click the index option to index the desired column as shown in screenshot:

enter image description here

Once I indexed both columns I was trying to reference with my foreign keys, I was able to successfully use the alter table and create the foreign key relationship. You will see that the columns are indexed like in the below screenshot:

enter image description here

notice how zip_code shows up in both tables.

The Answer 21

1 people think this answer is useful

You need check that both be same in all its properties, inclusive in “Collation”

The Answer 22

1 people think this answer is useful

I was using HeidiSQL and to solve this problem I had to create an index in the referenced table with all the columns being referenced.

adding index to table Heidisql

The Answer 23

1 people think this answer is useful

I ran into the same issue just now. In my case, all I had to do is to make sure that the table I am referencing in the foreign key must be created prior to the current table (earlier in the code). So if you are referencing a variable (x*5) the system should know what x is (x must be declared in earlier lines of code). This resolved my issue, hope it’ll help someone else.

The Answer 24

1 people think this answer is useful

My case was that I had a typo on the referred column:

MariaDB [blog]> alter table t_user add FOREIGN KEY ( country_code ) REFERENCES t_country ( coutry_code );
ERROR 1005 (HY000): Can't create table `blog`.`t_user` (errno: 150 "Foreign key constraint is incorrectly formed")

The error message is quite cryptic and I’ve tried everything – verifying the types of the columns, collations, engines, etc.

It took me awhile to note the typo and after fixing it all worked fine:

MariaDB [blog]> alter table t_user add FOREIGN KEY ( country_code ) REFERENCES t_country ( country_code );
Query OK, 2 rows affected (0.039 sec)              
Records: 2  Duplicates: 0  Warnings: 0

The Answer 25

0 people think this answer is useful

I had the same issue with Laravel 5.1 migration Schema Builder with MariaDB 10.1.

The issue was that I had typed unigned instead of unsigned(the s letter was missing) while setting the column.

After fixing the typo error was fixed for me.

The Answer 26

0 people think this answer is useful

Even i ran into the same issue with mysql and liquibase. So this is what the problem is: The table from which you want to reference a column of other table is different either in case of datatype or in terms of size of the datatype.

Error appears in below scenario:
Scenario 1:
Table A has column id, type=bigint
Table B column referenced_id type varchar(this column gets the value from the id column of Table A.)
Liquibase changeset for table B:

    <changeset id="XXXXXXXXXXX-1" author="xyz">
            <column name="referenced_id" **type="varchar"**>
        </column>
            </changeset>
    <changeSet id="XXXXXXXXXXX-2" author="xyz">
                <addForeignKeyConstraint constraintName="FK_table_A"
                    referencedTableName="A" **baseColumnNames="referenced_id**"
                    referencedColumnNames="id" baseTableName="B" />
    </changeSet>

Table A changeSet:

    <changeSet id="YYYYYYYYYY" author="xyz">
     <column **name="id"** **type="bigint"** autoIncrement="${autoIncrement}">
                    <constraints primaryKey="true" nullable="false"/>
                </column>
    </changeSet>

Solution: 
correct the type of table B to bigint because the referenced table has type bigint.

Scenrario 2:
The type might be correct but the size might not.
e.g. :
Table B : referenced column type="varchar 50"
Table A : base column type ="varchar 255"

Solution change the size of referenced column to that of base table's column size.

The Answer 27

0 people think this answer is useful

Check that you’ve specified name of the table in the proper case (if table names are case-sensitive in your database). In my case I had to change

 CONSTRAINT `FK_PURCHASE_customer_id` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`id`) ON UPDATE CASCADE ON DELETE CASCADE

to

 CONSTRAINT `FK_PURCHASE_customer_id` FOREIGN KEY (`customer_id`) REFERENCES `CUSTOMER` (`id`) ON UPDATE CASCADE ON DELETE CASCADE

note the customer changed to CUSTOMER.

The Answer 28

0 people think this answer is useful

Or you can use DBDesigner4 which has a graphical interface to create your database and linking them using FK. Right click on your table and select ‘Copy Table SQL Create’ which creates the code.

enter image description here

The Answer 29

0 people think this answer is useful

It’s an old subject but I discovered something. While building a MySQL workbench, it also gets the relationships of the other table. just leave the pillars you relate to. Clear other automatically added columns. This works for me.

The Answer 30

0 people think this answer is useful

I face this problem the error came when you put the primary key in different data type like:

table 1:

 Schema::create('products', function (Blueprint $table) {
            $table->increments('id');
            $table->string('product_name');
        });

table 2:

Schema::create('brands', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->string('brand_name');
        });

the data type for id of the second table must be increments

Add a Comment