MySQL error while creating foreign key in <ColumName> (check data types)

Question

I seem to be having problems setting up Foreign Key between my two tables.

Here is the CREATE clause for each table:

 CREATE TABLE IF NOT EXISTS `dbname`.`CallRecord` ( `id` INT NOT NULL AUTO_INCREMENT, `user_id` INT NOT NULL, `city_id` INT NOT NULL, `created` DATETIME NULL, `timestamp` TIMESTAMP NULL, PRIMARY KEY (`id`), INDEX `user_id_fk_idx` (`user_id` ASC), INDEX `city_id_fk_idx` (`city_id` ASC), CONSTRAINT `user_id_fk` FOREIGN KEY (`user_id`) REFERENCES `dbname`.`User` (`id`) ON DELETE RESTRICT ON UPDATE NO ACTION, CONSTRAINT `city_id_fk` FOREIGN KEY (`city_id`) REFERENCES `dbname`.`City` (`id`) ON DELETE RESTRICT ON UPDATE NO ACTION) ENGINE = InnoDB; 

And here is another table:

 CREATE TABLE IF NOT EXISTS `dbname`.`DataCallAssoc` ( `id` INT NOT NULL AUTO_INCREMENT, `data_id` INT NOT NULL, `call_record_id` INT NOT NULL, PRIMARY KEY (`id`), INDEX `data_id_fk_idx` (`data_id` ASC), INDEX `call_record_id_fk_idx` (`call_record_id` ASC), CONSTRAINT `data_id_fk` FOREIGN KEY (`data_id`) REFERENCES `dbname`.`Data` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION, CONSTRAINT `call_record_id_fk` FOREIGN KEY (`call_record_id`) REFERENCES `dbname`.`CallRecord` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION) ENGINE = InnoDB; 

The problem is the last CONSTRAINT of DataCallAssoc :

  CONSTRAINT `call_record_id_fk` FOREIGN KEY (`call_record_id`) REFERENCES `dbname`.`CallRecord` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION) 

I get this error:

Error creating foreign key on call_record_id (check data types)

Even when I create the table and foreign keys separately. All other foreign keys work, and even other tables that point to CallRecord.id as a foreign key work.

I also verified that CallRecord.id matches DataCallAssoc.call_record_id in terms of structure.

Answer 1

Error creating foreign key ... (data type checking) ALSO occurs if you already have a foreign key with the same name in another table. Therefore, if you encounter this most useless error message, make sure that your data types are the same and ALSO, the foreign key names are unique. Hope this helps someone.

Answer 2

In addition, for the data type to be the same, for some types, such as bigint (20), if the primary key is unsigned, then the foreign key must also be unsigned. "unsigned" is one of the attributes of the field.

Answer 3

I managed to recreate your tables in my local host, I removed this from the first create because you did not display the structure of the USER or City table, so the error should be there

 CONSTRAINT `user_id_fk` FOREIGN KEY (`user_id`) REFERENCES `dbname`.`User` (`id`) ON DELETE RESTRICT ON UPDATE NO ACTION, CONSTRAINT `city_id_fk` FOREIGN KEY (`city_id`) REFERENCES `dbname`.`City` (`id`) ON DELETE RESTRICT ON UPDATE NO ACTION) 

That is why I used.

 CREATE TABLE IF NOT EXISTS `CallRecord` ( `id` INT NOT NULL AUTO_INCREMENT, `user_id` INT NOT NULL, `city_id` INT NOT NULL, `created` DATETIME NULL, `timestamp` TIMESTAMP NULL, PRIMARY KEY (`id`), INDEX `user_id_fk_idx` (`user_id` ASC), INDEX `city_id_fk_idx` (`city_id` ASC) ) ENGINE = InnoDB CREATE TABLE IF NOT EXISTS `DataCallAssoc` ( `id` INT NOT NULL AUTO_INCREMENT, `data_id` INT NOT NULL, `call_record_id` INT NOT NULL, PRIMARY KEY (`id`), INDEX `data_id_fk_idx` (`data_id` ASC), INDEX `call_record_id_fk_idx` (`call_record_id` ASC), CONSTRAINT `call_record_id_fk` FOREIGN KEY (`call_record_id`) REFERENCES `CallRecord` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION) ENGINE = InnoDB