[SOLVED] Foreign Key Constrain Fails with "Error creating foreign key on [table] (check data types)"

Issue

The following query fails with error “Error creating foreign key on city (check data types)”:

ALTER TABLE  `hotels` ADD FOREIGN KEY (  `city` )
REFERENCES  `mydatabase`.`cities` (`id`)
ON DELETE CASCADE ON UPDATE CASCADE ;

Basically I want to have a ony-to-many relation between city.id and hotels.city.

Here are both tables:

CREATE TABLE IF NOT EXISTS `cities` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;


CREATE TABLE IF NOT EXISTS `hotels` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `city` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `city` (`city`)
) ENGINE=InnoDB;

Solution

The data types need to match:

cities.id int(11)
hotels.city bigint(20)

Needs to become either:

cities.id bigint(20)
hotels.city bigint(20)

Or:

cities.id int(11)
hotels.city int(11)

Depending on what your application needs.

Also worth mentioning is the fact that both need to be either signed or unsigned.

You may need to OPTIMIZE your tables after changing the data types to match.

Answered By – Mihai Stancu

Answer Checked By – Katrina (BugsFixing Volunteer)