0

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

mysql workbench message when trying to create foreign key

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
  • Hey @Rob, can you add to your question your SHOW CREATE TABLE\G statement? – oNare Aug 7 '15 at 14:25
  • Add the show create table for the other table, too. – ypercubeᵀᴹ Aug 7 '15 at 18:49
  • Added 3 table definitions. – Rob Aug 7 '15 at 21:27
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.

| improve this answer | |
  • This is a good suggestion, and may help other people with this issue, but it's not the problem for me. I first checked that the charset was the same (it wasn't), then I changed it to latin1_general_ci for both tables involved (since that's our db default), and it still won't let me add the constraint. I've also shortened the index and constraint names (replaced ArticleNote with ANote), and completely removed ArticleNote and recreated it. Still getting the same error. – Rob Aug 7 '15 at 16:41
  • Can we see both SHOW CREATE TABLEs. – Rick James Aug 7 '15 at 19:16
0

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
    ...
| improve this answer | |
  • You have cause and effect reversed. The column isn't there because MySQL Workbench won't allow me to add it (see image I posted in question). – Rob Aug 10 '15 at 14:17
  • This code (with the missing columns) is produced by Workbench, correct? – ypercubeᵀᴹ Aug 10 '15 at 14:20
  • Correct. I need workbench to get it correct so I can keep the model synchronized with the live database. – Rob Aug 10 '15 at 14:31
  • OK, so you are doing this is the model or in the database? My guess is that the two are not synchronized and what you are showing us as the CREATE TABLE Employee is from the database while the model has the table differently. – ypercubeᵀᴹ Aug 10 '15 at 14:33
  • No, everything I'm showing you is from the model. The commands you see are the commands that MySQL Workbench gives me to synchronize the database with what is in the model. – Rob Aug 10 '15 at 14:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.