Error when creating foreign key: MySQL Error 1215: Cannot add foreign key constraint

Question

I am currently coding a booking system for a company and they also wanted a task managment system but I have encountered an error which is driving me insane.

Executing SQL script in server
ERROR: Error 1215: Cannot add foreign key constraint
SQL Code:
        -- -----------------------------------------------------
        -- Table `bvsv_system`.`task`
        -- -----------------------------------------------------
        CREATE TABLE IF NOT EXISTS `bvsv_system`.`task` (
          `idtask` INT(11) NOT NULL,
          `attGora` VARCHAR(200) NULL,
          `status` VARCHAR(45) NULL,
          `to` VARCHAR(45) NULL,
          `jobstatus_id` INT(11) NOT NULL,
          `worker_personnummer` VARCHAR(45) NOT NULL,
          PRIMARY KEY (`idtask`, `jobstatus_id`, `worker_personnummer`),
          INDEX `fk_task_jobstatus1_idx` (`jobstatus_id` ASC),
          INDEX `fk_task_worker1_idx` (`worker_personnummer` ASC),
          CONSTRAINT `fk_task_jobstatus1`
            FOREIGN KEY (`jobstatus_id`)
            REFERENCES `bvsv_system`.`jobstatus` (`id`)
            ON DELETE NO ACTION
            ON UPDATE NO ACTION,
          CONSTRAINT `fk_task_worker1`
            FOREIGN KEY (`worker_personnummer`)
            REFERENCES `bvsv_system`.`worker` (`personnummer`)
            ON DELETE NO ACTION
            ON UPDATE NO ACTION)
        ENGINE = InnoDB

SQL script execution finished: statements: 15 succeeded, 1 failed

I get this error when i try to add a foreign key linking these two tables

CREATE TABLE IF NOT EXISTS `bvsv_system`.`worker` (
  `personnummer` VARCHAR(45) NOT NULL,
  `fornamn` VARCHAR(45) NULL DEFAULT NULL,
  `efternamn` VARCHAR(45) NULL DEFAULT NULL,
  `extraanstalld` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`personnummer`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;

and

CREATE TABLE IF NOT EXISTS `bvsv_system`.`task` (
  `idtask` INT(11) NOT NULL,
  `attGora` VARCHAR(200) NULL,
  `status` VARCHAR(45) NULL,
  `to` VARCHAR(45) NULL,
  `jobstatus_id` INT(11) NOT NULL,
  `worker_personnummer` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`idtask`, `jobstatus_id`, `worker_personnummer`),
  INDEX `fk_task_jobstatus1_idx` (`jobstatus_id` ASC),
  INDEX `fk_task_worker1_idx` (`worker_personnummer` ASC),
  CONSTRAINT `fk_task_jobstatus1`
    FOREIGN KEY (`jobstatus_id`)
    REFERENCES `bvsv_system`.`jobstatus` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_task_worker1`
    FOREIGN KEY (`worker_personnummer`)
    REFERENCES `bvsv_system`.`worker` (`personnummer`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

Help would be much appreciated my fellow coders! :)

score 2 · Accepted Answer · answered Aug 25 '18 at 18:43

The foreign key and primary key need to have the exact same definition. The worker table defines DEFAULT CHARACTER SET = latin1.

I'm not sure why you would want different character sets for different tables. I would recommend just using the database default.

If you do need them, then make the character sets compatible. You can do this at the column level:

CREATE TABLE IF NOT EXISTS `task` (
  `idtask` INT(11) NOT NULL,
  `attGora` VARCHAR(200) NULL,
  `status` VARCHAR(45) NULL,
  `to` VARCHAR(45) NULL,
  `jobstatus_id` INT(11) NOT NULL,
  `worker_personnummer` VARCHAR(45) CHARACTER SET latin1 NOT NULL,
  PRIMARY KEY (`idtask`, `jobstatus_id`, `worker_personnummer`),
  INDEX `fk_task_jobstatus1_idx` (`jobstatus_id` ASC),
  INDEX `fk_task_worker1_idx` (`worker_personnummer` ASC),
  CONSTRAINT `fk_task_worker1`
    FOREIGN KEY (`worker_personnummer`)
    REFERENCES `worker` (`personnummer`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
)
ENGINE = InnoDB

Error when creating foreign key: MySQL Error 1215: Cannot add foreign key constraint

1 Answers1