Richard Richard - 11 days ago 6
MySQL Question

MySQL: Foreign key constraint is incorrectly formed

I am using a MySQL database, have the following 3 tables:

map_range
enter image description here

map_location (join table)
enter image description here

location
enter image description here

I am trying to add some foreign keys.

ALTER TABLE `www`.`map_location`
ALTER TABLE `www`.`map_location`
ADD INDEX `fk_map_location_indx` (`LOC_ID` ASC);
ALTER TABLE `www`.`map_location`
ADD CONSTRAINT `fk_map_loc_map`
FOREIGN KEY (`MAP_ID`)
REFERENCES `www`.`map_location` (`MAP_ID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
ADD CONSTRAINT `fk_map_loc_location`
FOREIGN KEY (`LOC_ID`)
REFERENCES `www`.`location` (`ID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION;


But I get the following error:

Operation failed: There was an error while applying the SQL script to the database.
ERROR 1025: Error on rename of '.\www\#sql-ea0_2b8' to '.\www\map_location' (errno: 150 - Foreign key constraint is incorrectly formed)
SQL Statement:
ALTER TABLE `www`.`map_location`
ADD CONSTRAINT `fk_map_loc_map`
FOREIGN KEY (`MAP_ID`)
REFERENCES `www`.`map_location` (`MAP_ID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
ADD CONSTRAINT `fk_map_loc_location`
FOREIGN KEY (`LOC_ID`)
REFERENCES `www`.`location` (`ID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION


enter image description here

Any ideas appreciated.

Answer

You are self-refferencing the same table and colum.
did you mean to location and not map_location?

ALTER TABLE `www`.`map_location`
ADD CONSTRAINT... FOREIGN KEY (`MAP_ID`)
REFERENCES `www`.`map_location` (`MAP_ID`)