mysql Foreign key constraint is incorrectly formed error

mysql sql heidisql
user516883 picture user516883 · Dec 8, 2011 · Viewed 349.5k times · Source

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

Answer

Jake Wilson picture Jake Wilson · Jun 19, 2012

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.

Related questions

How to create table in MariaDB?

When I try to create simple table via HeidiSQL I'm getting an error like this CREATE TABLE `prg_config` ( `id` INT NULL AUTO_INCREMENT, `name` VARCHAR(50) NULL DEFAULT '', `value` VARCHAR NULL DEFAULT '' ) COLLATE='utf8_bin';

Read More
sql if exists update else insert not working

i have a table with 3 generic keys which are also foreign keys. This is my query -- IF (EXISTS(SELECT * FROM table1 WHERE col_1 =4)) BEGIN UPDATE table1 SET col_2 = 3, col_3 = 100 WHERE col_1 = 4 END ELSE BEGIN INSERT INTO table1 (col_1, col_2, …

Read More
generate sql using HeidiSQL

I would like to ask is there any config that allow HeidiSQL to generate INSERT query that look like: INSERT INTO state SET ST_ID = NULL, ST_NAME = "lol" instead of: INSERT INTO state (ST_ID, ST_NAME) VALUES (NULL, …

Read More