Create foreign key doesn't work, column types match

Question

I'm trying to create a foreign key in mysql workbench, but keep getting this error:

I've checked the columns on both tables, and they match up as well as I can make them. One is CHAR(21) not null primary key, and the other is CHAR(21) not null.

Edit (again): (both tables)

First, the original table that I'm trying to link to:

    CREATE TABLE IF NOT EXISTS `db`.`Employee` (
      `employeeId` CHAR(21) NOT NULL,
      `departmentId` CHAR(21) NULL DEFAULT NULL,
      `chatStatusId` CHAR(21) NOT NULL DEFAULT 'd15a558946afbbd4a6046',
      `employeeNum` INT(4) NOT NULL,
      `payrollNum` INT(11) UNSIGNED NULL DEFAULT '0',
      `title` VARCHAR(50) NULL DEFAULT NULL,
      `dateHired` DATE NOT NULL,
      `terminationDate` DATE NULL DEFAULT NULL,
      `LastLogin` DATETIME NULL DEFAULT NULL,
      `rightMask` INT(11) NOT NULL COMMENT 'we should be using the right table instead',
      `teamId` INT(11) UNSIGNED NULL DEFAULT NULL COMMENT 'this is no longer really used',
      `IsTeamLead` TINYINT(4) NOT NULL DEFAULT '0',
      `bioId` INT(11) UNSIGNED NULL DEFAULT NULL,
      `emailSignature` TEXT NULL DEFAULT NULL,
      `notes` TEXT NULL DEFAULT NULL,
      PRIMARY KEY (`employeeId`),
      UNIQUE INDEX `employeeNum` (`employeeNum` ASC),
      INDEX `departmentId` (`departmentId` ASC),
      INDEX `chatStatusId` (`chatStatusId` ASC),
      INDEX `teamId` (`teamId` ASC),
      CONSTRAINT `Employee_ibfk_1`
        FOREIGN KEY (`employeeId`)
        REFERENCES `db`.`Person` (`personId`)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
      CONSTRAINT `Employee_ibfk_10`
        FOREIGN KEY (`teamId`)
        REFERENCES `db`.`HaloTeam` (`teamId`)
        ON DELETE RESTRICT
        ON UPDATE RESTRICT,
      CONSTRAINT `Employee_ibfk_8`
        FOREIGN KEY (`departmentId`)
        REFERENCES `db`.`Department` (`departmentId`),
      CONSTRAINT `Employee_ibfk_9`
        FOREIGN KEY (`chatStatusId`)
        REFERENCES `db`.`ChatStatus` (`chatStatusId`)
        ON DELETE RESTRICT
        ON UPDATE RESTRICT)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1
COLLATE = latin1_general_ci

And this is the new table I'm trying to create a foreign key on to the original table:

CREATE TABLE IF NOT EXISTS `db`.`ArticleNote` (
  `ArticleNoteID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `ArticleID` INT(10) UNSIGNED NOT NULL,
  `Created` TIMESTAMP NOT NULL,
  `employeeId` CHAR(21) NOT NULL,
  `Note` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`ArticleNoteID`),
  INDEX `fk_ANote_Article_idx` (`ArticleID` ASC),
  INDEX `fk_ANote_Employee_idx` (`employeeId` ASC),
  CONSTRAINT `fk_ANote_Article`
    FOREIGN KEY (`ArticleID`)
    REFERENCES `db`.`Article` (`ArticleID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_ANote_Employee`
    FOREIGN KEY ()
    REFERENCES `db`.`Employee` ()
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1
COLLATE = latin1_general_ci

And this is an example of a table that already has a foreign key (using the same column) as the one I'm trying to create in my new table:

CREATE TABLE IF NOT EXISTS `db`.`Article` (
  `ArticleID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `ArticleCategoryID` INT(10) UNSIGNED NOT NULL DEFAULT 1,
  `Url` VARCHAR(120) NOT NULL,
  `Title` VARCHAR(60) NOT NULL,
  `Summary` VARCHAR(255) NOT NULL,
  `Image` VARCHAR(200) NULL,
  `StatusIDE` INT(11) NOT NULL,
  `AreaIDE` INT(11) NULL,
  `CreatedAt` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `CreatedBy` CHAR(21) NULL DEFAULT NULL,
  `ExpiresAt` TIMESTAMP NULL DEFAULT NULL,
  `PayoutAmount` DECIMAL(11,2) NOT NULL DEFAULT 0.00,
  PRIMARY KEY (`ArticleID`),
  INDEX `fk_Aritcle_Category_idx` (`ArticleCategoryID` ASC),
  INDEX `fk_Article_Status_idx` (`StatusIDE` ASC),
  INDEX `fk_Article_Area_idx` (`AreaIDE` ASC),
  INDEX `fk_Article_Employee1_idx` (`CreatedBy` ASC),
  CONSTRAINT `fk_Article_Employee1`
    FOREIGN KEY (`CreatedBy`)
    REFERENCES `db`.`Employee` (`employeeId`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Article_ArtCat1`
    FOREIGN KEY (`ArticleCategoryID`)
    REFERENCES `db`.`ArticleCategory` (`ArticleCategoryID`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `fk_Article_Status`
    FOREIGN KEY (`StatusIDE`)
    REFERENCES `db`.`DatabaseEnum` (`DatabaseEnumID`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `fk_Article_Area`
    FOREIGN KEY (`AreaIDE`)
    REFERENCES `db`.`DatabaseEnum` (`DatabaseEnumID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1
COLLATE = latin1_general_ci

Answer 1

Probably they are not both CHARACTER SET utf8 and COLLATE utf8_general_ci.

I strongly suspect you do not need utf8 for an employeeId. I recommend you explicitly make that column CHARACTER SET ascii COLLATE ascii_bin (or ascii_general_ci if you need case folding) if you don't have any accented or Chinese (etc) characters in the ids.

What you have (CHAR(21) utf8) makes that column and, any indexes involving it, take 63 bytes instead of just 21.

Answer 2

The problem is from this part of the SQL code. The Foreign Key constraint definition is missing the column to be used:

CREATE TABLE IF NOT EXISTS `db`.`ArticleNote` (
  `ArticleNoteID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
   ---
   --- several lines removed
   ---
  CONSTRAINT `fk_ANote_Employee`
    FOREIGN KEY ()                  --<-- missing column here
    REFERENCES `db`.`Employee` ()   --<-- and here
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1
COLLATE = latin1_general_ci

Should be:

  CONSTRAINT `fk_ANote_Employee`
    FOREIGN KEY (employeeId)                  
    REFERENCES `db`.`Employee` (employeeId)   
    ON DELETE NO ACTION
    ...