FK constraint is incorrectly formed ERROR

I got the following error message whenever I try to import this sql file to my localhost

(#1005 - Can't create table hostel_management_system.Hostel_Manager (errno: 150 "Foreign key constraint is incorrectly formed"))

Here is the sql:

DROP TABLE IF EXISTS `Hostel_Manager`;

CREATE TABLE `Hostel_Manager` (
  `Hostel_man_id` int(10) NOT NULL AUTO_INCREMENT,
  `Username` varchar(255) NOT NULL,
  `Fname` varchar(255) NOT NULL,
  `Lname` varchar(255) NOT NULL,
  `Mob_no` varchar(255) NOT NULL,
  `Hostel_id` int(10) NOT NULL,
  `Pwd` LONGTEXT NOT NULL,
  `Isadmin` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`Hostel_man_id`),
  UNIQUE (`Username`),
  KEY `Hostel_id` (`Hostel_id`),
  CONSTRAINT `Hostel_Manager_ibfk_1` FOREIGN KEY (`Hostel_id`) REFERENCES `Hostel` (`Hostel_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


LOCK TABLES `Hostel_Manager` WRITE;

UNLOCK TABLES;

Hostel table :

CREATE TABLE `Hostel` (
  `Hostel_id` int(10) NOT NULL AUTO_INCREMENT,
  `Hostel_name` varchar(255) NOT NULL,
  `current_no_of_rooms` varchar(255) DEFAULT NULL,
  `No_of_rooms` varchar(255) DEFAULT NULL,
  `No_of_students` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`Hostel_id`)
)

Why do I get that error and how to fix it?

Solution

Both the parent and children column have the same datatype and length, and the parent column is a primary colum, so that's not the issue here.

I suspect that the problem is the charset. Hostel_Manager specifies a default charset, while Hostel does not. If the default charset of your database is something else than latin1, then the foreign is malformed.

I would recommend explictly aligning the charset so it is the same for both tables (either remove both, or declare the same value).

Note that both tables also need to have the same storage engine. InnoDB is the default, so that should not be an issue, but you might want to explictly align that as well (in case the default of your database is MyISAM).