-2

I want to create mysql table like:

  CREATE TABLE `departments` (
  `department_id`   int(4) NOT NULL AUTO_INCREMENT,
  `department_name` varchar(3) DEFAULT NULL,
  `manager_id`      int(6) DEFAULT NULL,
  `location_id`     int(4) DEFAULT NULL,
  PRIMARY KEY (`department_id`),
  KEY `loc_id_fk` (`location_id`),
  CONSTRAINT `loc_id_fk` FOREIGN KEY (`location_id`) REFERENCES `locations` (`location_id`)
) ENGINE=InnoDB AUTO_INCREMENT=271 DEFAULT CHARSET=gb2312;

And it reports the error: Can't create table `myemployees`.`departments` (errno: 150 "Foreign key constraint is incorrectly formed") So how can I solve this?

Appreciate!

ARPATNURMAMAT
  • 11
  • [Form the foreign key correctly](https://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html). – RobIII Oct 12 '22 at 12:34

1 Answers1

4

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.

reference link you can see to references file about that error. i hope that's helping you. thanks

Rezky RVL
  • 108
  • 5
  • thanks for your solution! I just find that may be we can `SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0` before we create table and it works, don't forget `SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS` after creating table – ARPATNURMAMAT Oct 12 '22 at 12:49
  • you wanna create foreign key like this ? [link] (https://ibb.co/KjdsS5V) [link] – Rezky RVL Oct 12 '22 at 13:04