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

Question

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;

Answer 1

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.

Answer 2

I was using phpMyAdmin and i tried creating multiple indices on different tables using the relations view. However, I got the same error saying datatypes did not match. However, the cause indeed was that i was giving the same foreign key name to multiple relations, and because of duplicate names, mysql was throwing this error. So rename your relation, and it should work fine.

Answer 3

I know this is quite an old thread, but I spent some time with this error as well.

The situation I had was the following one:

Table 1: administrations (Primary key: AdministrationId) Table 2: invoices (Foreign key to AdministrationId) Table 3: users (error pops up while creating foreign key)

The colomns AdministrationId in my invoices and users table were both of the same type as the AdministrationId column in the administrations table.

The error on my side was that I tried to create a foreign key called administration_id in my users table. But a minute before that I already created a foreign key in my invoices table also called administration_id. When I tried to give the foreign key another name, it worked out fine.

Therefore, keep in mind to correctly name your foreign keys (e.g. prefix them with the table name, eg: invoices_administration_id and users_administration_id). Multiple foreign keys with the same name may not exist (within the same database).

Answer 4

I know this has been answered and I know this question is old. However, I just came across this same error with a different cause and, since this is the top result for this error, I thought I would put this information here both for my own use in the future as well as anyone else who happens along after me.

My columns were both bigint and unsigned. However, after first creating the referenced tables, I then went on to change the name of the primary key column. Nothing else about it had changed, but I was unable to create a foreign key relationship. I ended up dropping the referenced tables and recreating them using the column names I desired and I was able to create the foreign key relationships.

Answer 5

Worth mentioning, but the collation should be the same between both table

I faced the same issue with varchar(64) fields in both tables, and It took me some time to identify the problem was coming from the collation field which was not the same between the 2 table fields.

Answer 6

Updating hotels.city to unsigned worked for me. Because cities.id is unsigned

Answer 7

I'd like to point out, you will get a similar error in case you have set the foreign key to NOT NULL and you have set either the ON DELETE or ON UPDATE to SET NULL.

Answer 8

Udating data type cities.id bigint(20) and hotels.city bigint(20) OR Udating data type cities.id int(11) and hotels.city int(11) AND Updating hotels.city to unsigned because cities.id is unsigned.