MySQL错误在< ColumName>上创建外键（检查数据类型） [英] MySQL Error creating foreign key on <ColumName> (check data types)

问题描述

我似乎无法在两张表之间设置外键。

每个表的 CREATE 子句：

  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; 
  

以下是另一张表：

  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; 
  

问题在于最后一个 CONSTRAINT DataCallAssoc ：

  CONSTRAINT`call_record_id_fk` 
 FOREIGN KEY `call_record_id`）
 REFERENCES`dbname`.`CallRecord`（`id`）
 ON DELETE CASCADE 
 ON UPDATE NO ACTION）
  pre> 
 
 
我得到这个错误：
 $ b 
 在call_record_id上创建外键时出错检查数据类型） 
 
 
 
即使我分别创建表和外键。每一个其他的外键都可以工作，甚至其他指向 CallRecord.id 的表也可以作为外键使用。
 
 
 
我还检查了 CallRecord.id 与 DataCallAssoc.call_record_id 的结构相同。 b $ b 
解决方案
错误创建外键...（检查数据类型）错误如果在另一个表中已经有一个相同名称的外键。所以，如果你遇到这个最无用的错误信息，确保你的数据类型匹配，并且外键名是唯一的。希望这可以帮助别人。
 
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.
解决方案
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.

                        
这篇关于MySQL错误在< ColumName>上创建外键（检查数据类型）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！