mysql Foreign key constraint is incorrectly formed error

Question

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

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

Solution 2

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

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

Solution 4

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

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

View more solutions

Comments

[Romain]

What engine are you using for your tables? What's the type of table2.IDFromTable1and table1.ID?

[Carsten]

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

[user516883]

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

[Devart]

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.

[Álvaro González]

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

[Devart]

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

[Sudhir Bastakoti]

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

[Ben]

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.

[nawfal]

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

[Alkanshel]

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

[cbloss793]

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!

[SaidbakR]

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

[quazardous]

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

[jave.web]

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

[jave.web]

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

[Paul Carlton]

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

[philipxy]

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

[JonnyJD]

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

[Ne Ma]

Keys not being indexed were my issue.

[Khatri]

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

[hendr1x]

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.

[Neek]

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

[Itai]

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

[hendr1x]

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

[kevenlolo]

You made my day.

[Renat Gatin]

Glad to help! ;)

[Xmanoux]

Actually it's FOREIGN_KEY_CHECKS

[leuchtdiode]

Even utf8_unicode_ci and utf8_general_ci are causing the error

[Halvor Holsten Strand]

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

[Barmar]

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

[Dewlance]

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

[Sam_Butler]

Yup, mine was mismatched signed/unsigned integer types.

[Firze]

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.

[bumerang]

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

[Alex Barker]

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

[Béatrice Cassistat]

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

[rjkunde]

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

[botdotcom]

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

[MarthyM]

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.

[Robert Franklin]

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

[Jerome]

Save my day~~~~ Thanks a lot

[Sidonai]

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

[Newton Munene]

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

[Ebrahim Bashirpour]

Sene Qoraban qardash

[Sebastian Kaczmarek]

Thank you, it saved me hours

[Pascal]

Thank you, this was so much more useful than trying all the other answers here.

[Kdawg]

Definitely! This feels like it really should be the accepted answer, as it shows what's actually wrong with it rather than just listing one or two of the reasons that this could happen..

[Black]

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

[WNRosenberg]

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.

[Spaceploit]

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

[Omar]

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

[Peter VARGA]

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.

[Dawid Gałecki]

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

[Dave]

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?

[Barbz_YHOOL]

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

[Emoon]

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

[Stanley Aloh]

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

[Arvind K.]

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

[downdrown]

You, Sir, just saved me hours of my time! Thank you very much!

[Hermsi1337]

Ooof - You are a god! Thanks mate!

[Code Slicer]

10 years later, this saved me on Magento 2, thanks.