Problem
When I add an FK on IDFromTable1 to ID in table1, I get the error Foreign key constraint is erroneously created. I have two tables, table1 is the parent table with a column ID and table2 has a column IDFromTable1 (not the actual name). If table 1 is deleted, I’d like to delete table 2 as well. Thank you for your assistance.
ALTER TABLE `table2`
ADD CONSTRAINT `FK1`
FOREIGN KEY (`IDFromTable1`) REFERENCES `table1` (`ID`)
ON UPDATE CASCADE
ON DELETE CASCADE;
Please let me know if you require any other information. Mysql is new to me.
Asked by user516883
Solution #1
With HeidiSQL, I had a similar issue. The error message you get is rather cryptic. The foreign key column and the reference column were not of the same type or length, which was the source of my difficulty.
The referenced column was INT(10) UNSIGNED, while the foreign key column was SMALLINT(5) UNSIGNED. The foreign key creation worked fine when I made them both the same exact type.
Answered by Jake Wilson
Solution #2
When I used the MyISAM engine to generate the parent table, I ran into the identical issue. It was a foolish oversight, which I corrected with:
ALTER TABLE parent_table ENGINE=InnoDB;
Answered by Denis Malinovsky
Solution #3
Anyone experiencing this issue should execute SHOW ENGINE INNODB STATUS and look at the LATEST FOREIGN KEY ERROR section for more information.
Answered by Sidonai
Solution #4
Make that the columns are identical (of the same type) and that the reference column is INDEXED if it is not a primary key.
Answered by Santosh
Solution #5
The syntax for defining foreign keys is extremely forgiving, but anyone else who gets stuck should remember that the requirement that foreign keys be “of the same type” applies to collation as well as data type, length, and bit signing.
You wouldn’t mix collation in your model, right? If you do, make sure your main and foreign key fields in phpmyadmin or Heidi SQL (or whatever database management system you use) are of the same collation type.
I hope this saves you the four hours of trial and error that it took me to figure it out.
Answered by user2297047
Post is based on https://stackoverflow.com/questions/8434518/mysql-foreign-key-constraint-is-incorrectly-formed-error