mysql Foreign key constraint is incorrectly formed error
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
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.
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;
For anyone facing this problem, just run
SHOW ENGINE INNODB STATUS
and see the LATEST FOREIGN KEY ERROR section for details.
make sure columns are identical(of same type) and if reference column is not primary_key
, make sure it is INDEXED
.