MySQL Error creating foreign key on <ColumName> (check data types)

Question

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.

Briannebriano

Answer 1

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.

Rightly

I would not have figured this out. Is it fine just to let phpMyAdmin auto generate a constraint name? – Hopscotch

So long as you consistently use the auto-generated names (leave the fk field blank), then you won't have a cross-table "clash" because phpMyAdmin prefixes the names with the current table, then adds the fk number to the end. So basically: No, I don't think you would encounter this particular error situation. On the other hand, it may make it more difficult to troubleshoot, as your foreign keys aren't named more succinctly/deliberately. – Rightly

Okay. I can rename my foreign keys to something more readable while using the table prefix. I wasn't adding the prefixes, which is what landed me here. – Hopscotch

this was helpful. – Sponsor

Thanks! That was my problem. Did not know the names had to be unique between tables. – Vicechancellor

Answer 2

As well as making sure the data-type is the same, for some types, such as bigint(20), if the primary-key is unsigned then the foreign-key must be unsigned as well. "unsigned" is one of the attributes of a field.

Turtleback

Answer 3

I was able to recreate your tables in my localhost, I deleted this from the first create, because you didn't display the structure of the USER or the 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) 

this 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

Tuatara