mysql Foreign key constraint is incorrectly formed error

Question

table1 is the parent table with a column ID and table2 has a column IDFromTable1.

Why when I put a FK on IDFromTable1 to ID in table1 do I get Foreign key constraint is incorrectly formed error?

(I would like to delete the table2 record if the table1 record gets deleted.)

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

Both tables' engines are InnoDB. Both colmnns are type char. ID is the primary key in table1.

Deputation

Also, check that the character sets for both tables are the same. – Ingeingeberg

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

@Deputation - Do you need help to get table definitions? In HeidiSQL you can simply click on the CREATE code tab. – Bystander

...and what about table definitions? Run SHOW CREATE TABLE table1, then table2 – Astera

I had the same error. I ran "SHOW ENGINE INNODB STATUS" and found out I didn't have the required privileges. Using C-panel on a shared server didn't give me the privileges to ALTER, so I had to export the SQL, add the constraints and import. – Ricoriki

Answer 1

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.

Unqualified

Or may be that the referenced column is not a primary key – Murrey

Kinda similar problem for me--the referenced table didn't exist yet. Whoops. – Scarlettscarp

I've totally experienced what Jake did, but I've ran into another FK issue (different type) on HeidiSQL. FK on varchars need to be the same collation. Hope that helps someone else in the future! – Salford

In addition and for HeidiSQL too, You may forgot set a primary key in the referenced table which should be the foreign key. – Waspish

[Dumb mode on] If you set the FK field to have NOT NULL on an already populated table, MySQL will fill with 0 values... And when you try to add the constraint of course it will fail because you have no referenced record with PK = 0... – Mind

To add to @Amalgovinus - It DOES NOT tell you if referenced table doesn't exist, it will just lie to you saying "Foreign key constraint is incorrectly formed" . – Kayleen

In my case it was because of different Encoding and Collation. – Ninetieth

@Murrey - I believe it does not necessarily have to be a primary key, but it MUST have an index. Primary keys automatically get indexed. – Raster

Yup, mine was mismatched signed/unsigned integer types. – Hutcheson

No one mentioned partitioning. If your FK is a PK that is also the partition key, it will produce this error. – Gothar

This comment helped me. In my case it was because of a different encoding "varchar(36) COLLATE utf8mb4_general_ci DEFAULT NULL" (generated by hibernate) against a foreign key defined with "varchar(36) DEFAULT NULL". – Soppy

Also using HeidiSQL, resolved by changing the names of the FK's. They have to be unique! – Prefix

For me it was 'id INT(5) UNSIGNED NOT NULL' in one table, being referenced to by 'userid INT(5)'. Just added UNSIGNED NOT NULL and it worked like a charm :) – Dru

I don't understand your answer, a few screenshots would be helpful – Syndesis

One field being unsigned and the other not will also cause this error! – Audacity

In my case it's because I had "SET NULL" on a column that couldn't be NULL, mysql always so bad at explaining issues – Visage

In my case, it was because I had my composite foreign key columns in the wrong order compared to the composite pkey in the parent table. Still errored even though all my types and everything else matched. – Distich

Answer 2

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

Hereabouts

Awesome, this led me to the real error: Field type or character set for column 'vehicle_id' does not match referenced column 'id' – Levina

Thanks! I didn't now about this instruction, helped me figure out what went wrong. – Crotchety

This is by far the most efficient way to uncover the error, which could have numerous causes. – Paniculate

First off.. Dude.. My dude. Thank you! You'd think the "you have defined a SET NULL condition but column 'col' is defined as NOT NULL" would be in the actual error message?? – Knoxville

Interesting. That showed exactly what was going on. – Lally

Thanks a lot. This inspired me to improve the error message in HeidiSQL, which now appends the relevant content from that SHOW... command. – Stylistic

Answer 3

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;

Mesomorph

Thank you so much, i was going crazy with this. Any idea why the database would suddenly switch the engine for tables? – Debug

Answer 4

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

Yardstick

It even happened to me that, there was no error, but foreign key was not added (1 was and 1 wasn't actually), but after adding simple KEY referencing_column(referencing_column) BEFORE both foreign keys definition they were both added successfuly :) – Kayleen

Keys not being indexed were my issue. – Cleanup

I had this issue and the problem was I had a dual column primary key and you can not use the 2nd column of the primary key as a foreign key. So I just added own index for the 2nd column of the primary key and then it worked. – Enter

Answer 5

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.

Cryptography

Thanks! Turns out my online host uses the ISAM engine and for local dev I use InnoDB. When I backed up a table from the host to the local...boom. – Conan

Recent versions of MariaDB seem to use utf8_mb4 as the default charset (when not set explicitly in the server config) so COLLATE utf8mb4_unicode_ci was my (unexpected) problem (on the dev machine). – Dumas

Can't say about exactly four hours, but you definitely saved me a lot of wasted time and headaches! – Mirabella

Answer 6

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.

Bohi

Unsure of the details, but I had a composite key with this error which was fixed by adding individual unique indexes for the columns. – Winkle

The referenced column has to be indexed, it doesn't have to be unique (although that's the usual case). – Couteau

Answer 7

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

Stenger

God bless you. I have been struggling with this for almost an hour now. – Sledge

I ask myself - when I struggle in such cryptic error messages - who is proposing the error messages. May be there is a context who can create the most cryptic and misleading error message. Thank you! I had exactly this problem in the table design. – Naif

Answer 8

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.

Abstemious

Even utf8_unicode_ci and utf8_general_ci are causing the error – Dutton

@S Doering, you're right. In addition to the missing definition of my foreign key (that is VARCHAR) as unique index (while it's not a primary key in the referenced table), I had to declare explicitly COLLATE with the same value of my origin table. – Breeches

Oh yes! That was it for me... Executed Django migrations in a new different database and the default collation was another then the ones used before migrations... Thus I had OperationalError with foreignkey incorrectly formed, changing database collation to right one before migration fixed it for me... Thanks! – Tremendous

In my case I changed latin1 to utf8mb4 - and this fixed the problem – Sesterce

Answer 9

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.

Crumpet

This did help me to pass further, but my problem was missing primmary index in column – Chorography

I was encountering this issue after I deleted a table which had a foreign key constraint, and was trying to recreate the table and foreign key. Weird thing was, phpMyAdmin was telling me the foreign key had also been deleted, but MySQL was still throwing this error as if it still existed when I would run my create query. Restarting MySQL and then setting FOREIGN_KEY_CHECKS=OFF fixed whatever was wrong for my edge case, allowing me to create the table again. – Rubstone

This worked only for one of the tables, the rest I still get the same error. I assume there is some metadata column I am unaware of, or capitalization? – Valois

It certainly helped! I had to import a 3GB database created ages ago and FOREIGN_KEY_CHECKS=OFF did the trick! Thanks you! – Amadus

In a Terminal you can use mysql --init-command="SET SESSION FOREIGN_KEY_CHECKS=0;" ... see https://mcmap.net/q/82749/-disabling-foreign-key-checks-on-the-command-line – Scincoid

Answer 10

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

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

Reclinate

In my case "check the datatype of both fields. they must be the same" was my solution. Thank you, @Reclinate :) – Anjanette

Answer 11

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.

Calling

Answer 12

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

Oscitancy

Answer 13

This problem also occur in Laravel when you have the foreign key table table1 migration after the migration in which you reference it table2.

You have to preserve the order of the migration in order to foreign key feature to work properly.

database/migrations/2020_01_01_00001_create_table2_table.php
database/migrations/2020_01_01_00002_create_table1_table.php

should be:

database/migrations/2020_01_01_00001_create_table1_table.php
database/migrations/2020_01_01_00002_create_table2_table.php

Chomp

Answer 14

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

Chalcedony

Good point! I am dealing with a Zen Cart database in MySQL whose tables are all in the MyISAM engine by default. I added a table using InnoDB engine, and tried to add a foreign key constraint from my table to the core Zen Cart one. It failed with this obscure 'incorrectly formed' error. You can see the engine for each table with SHOW TABLE STATUS LIKE 'table_name'; – Metal

Answer 15

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;

Delvecchio

Its worked for me. I was worried that I need to reinstall my software ;) – Uxorious

Answer 16

I lost for hours for that!

PK in one table was utf8 in other was utf8_unicode_ci!

Radbun

Answer 17

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;

Warrant

Try running SHOW ENGINE INNODB STATUS to get some more details on the error – Warrant

@SudhirBastakoti - +1! That did it for me. The details are helpful. Was able to fix the problem quickly. – Capitalist

@SudhirBastakoti thank you man, For anyone facing this problem, run SHOW ENGINE INNODB STATUS and check the "LATEST FOREIGN KEY ERROR" section. – Hereabouts

Answer 18

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

Embezzle

Answer 19

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.

Middleaged

It doesn't need to be a PK, it can also be UNIQUE NOT NULL. – Likker

Actually...in my case simply setting it to an index type normal worked. – Eisk

Answer 20

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;

Schweitzer

This was my case too. my collation were different for the Varchar type, as soon as I changed the collation with phpMyAdmin Client everything worked – Robi

Answer 21

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

Dicky

Answer 22

For anyone struggling as I was with this issue, this was my problem:

I was trying to alter a table to change a field from VARCHAR(16) to VARCHAR(255) and this was referencing another table column where the datatype was still VARCHAR(16)...

Quizzical

Answer 23

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

Laevorotation

Answer 24

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.

Fibered

Same for me in mysql : InnoDB requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. – Eisk

Answer 25

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:

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:

notice how zip_code shows up in both tables.

Dufour

Answer 26

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.

Sender

I've had the same problem in MariaDB v10.3.18. We used MySQL before and it warned that a foreign key pointed to a non-existent table. – Francesco

Answer 27

The problem is very simple to solve

e.g: you have two table with names users and posts and you want create foreign key in posts table and you use phpMyAdmin

1) in post table add new column (name:use_id | type: like the id in user table | Length:like the id in user table | Default:NULL | Attributes:unsigned | index:INDEX )

2)on Structure tab go to relation view (Constraint name: auto set by phpmyAdmin | column name:select user_id |table:users | key: id ,...)

It was simply solved

javad mosavi iran/urmia

Getter

Answer 28

I had the same error, and I discovered that on my own case, one table was MyISAM, and the other one INNO. Once I switched the MyISAM table to INNO. It solved the issue.

Bleb

Answer 29

One more solution which I was missing here is, that each primary key of the referenced table should have an entry with a foreign key in the table where the constraint is created.

Sunny

Answer 30

If U Table Is Myisum And New Table Is InoDb you Are Note Foreign You Must Change MyIsum Table To InoDb

Designed

Answer 31

Table Name is CASE Sensitive:

In my case, the problem was incorrect table name. My table name was Users and I wrongly defined the foreign key as .. REFERENCES user(id). (Note the uppercase U in user).

Shindig

Answer 32

errno: 150 "Foreign key constraint is incorrectly formed also appears when you try to reference a key from a partitioned table. Remember that you cannot have references to the pk of partitioned tables.

Scientist

Answer 33

Another Laravel issue can be the order in the down method, you have to drop the referencing table first. Hope that makes sence !

Autry

Answer 34

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.

Innumerable

Answer 35

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.

Floatation

Answer 36

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.

Sparrow

Answer 37

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.

Flemish