MySQL Error creating foreign key on <ColumName> (check data types)
foreign-key-relationship, foreign-keys, mysql
Solution
The Error Creating Foreign Key...(check data types) error ALSO occurs if you already have a foreign key of the same name in another table. So if you run into this most unhelpful error message, make sure your data types match and ALSO foreign key names are unique. Hope this helps someone.
Problem
I seem to be having trouble setting up a `Foreign Key` between two of my 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 the other 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 lies with 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 am getting this error: `Error creating foreign key on call_record_id (check data types)` Even when I create the table and foreign keys separately. Every other foreign keys work, and even other tables that point to `CallRecord.id` as a foreign key works. I also checked that `CallRecord.id` is the same as `DataCallAssoc.call_record_id` in terms of structure.