Error 1215: Cannot add foreign key constraint

Question

I built a database with mySql Workbench, but when I try to forward engineer my model to the server, I get the following error :

ERROR: Error 1215: Cannot add foreign key constraint

followed by the definition of the table where the foreign key is defined, salaire_annee_ca

I read similar topics to identify the usual causes for this error, and checked :

• if the foreign key defined in salaire_annee_ca references the primary key of another table, which it does
• if something in the code allowed my key to be null, which it doesn't
• if the types of the reference and of the foreign key were the same

It seems to me that all these conditions are ok, so I don't understand why I still get that message. Here are the definitions of my tables :

These are the two main ones :

---

-- Table `credit_impot_db`.`salaires_annee`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `credit_impot_db`.`salaires_annee` (
  `salaire_annee_id` INT(11) NOT NULL ,
  `salaire_annuel` DOUBLE NOT NULL DEFAULT 0 ,
  `heures_travaillees` DOUBLE NOT NULL DEFAULT 0 ,
  `pourcentage_rsde` DOUBLE NOT NULL DEFAULT 0 ,
  `jours_travailles` INT(3) NOT NULL DEFAULT 0 ,
  PRIMARY KEY (`salaire_annee_id`) ,
  CONSTRAINT `salaire_annee_id`
    FOREIGN KEY (`salaire_annee_id` )
    REFERENCES `credit_impot_db`.`employes_ac` (`employe_ac_id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

This one is at the origin of the message :
-- -----------------------------------------------------
-- Table `credit_impot_db`.`salaire_annee_ca`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `credit_impot_db`.`salaire_annee_ca` (
  `salaire_annee_ca_id` INT(11) NOT NULL ,
  PRIMARY KEY (`salaire_annee_ca_id`) ,
  CONSTRAINT `salaire_annee_ca_id`
    FOREIGN KEY (`salaire_annee_ca_id` )
    REFERENCES `credit_impot_db`.`salaires_annee` (`salaire_annee_id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

And the following two are also referenced :

-- -----------------------------------------------------
-- Table `credit_impot_db`.`employes`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `credit_impot_db`.`employes` (
  `employe_id` INT(11) NOT NULL AUTO_INCREMENT ,
  `employe_nom` VARCHAR(255) NOT NULL ,
  `employe_prenom` VARCHAR(255) NOT NULL ,
  `employe_fonction` VARCHAR(255) NULL ,
  `employe_experience` VARCHAR(255) NULL DEFAULT NULL ,
  PRIMARY KEY (`employe_id`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `credit_impot_db`.`employes_ac`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `credit_impot_db`.`employes_ac` (
  `employe_ac_id` INT(11) NOT NULL AUTO_INCREMENT ,
  `fk_employe_ac_employe_id` INT(11) NULL ,
  `fk_employe_ac_ac_id` INT(11) NULL ,
  PRIMARY KEY (`employe_ac_id`) ,
  INDEX `fk_employe_ac_employe_id_idx` (`fk_employe_ac_employe_id` ASC) ,
  INDEX `fk_employe_ac_ac_id_idx` (`fk_employe_ac_ac_id` ASC) ,
  CONSTRAINT `fk_employe_ac_employe_id`
    FOREIGN KEY (`fk_employe_ac_employe_id` )
    REFERENCES `credit_impot_db`.`employes` (`employe_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_employe_ac_ac_id`
    FOREIGN KEY (`fk_employe_ac_ac_id` )
    REFERENCES `credit_impot_db`.`dossier_client` (`ac_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

Any help would be appreciated!

Answer 1

I hit the dreaded error 1215 while using the Workbench and spent a long time trying to work out what was wrong. I eventually noticed that some of my tables were defined with "latin1 - default collation", while others were defined with "Schema Default". I had to expand the table definitions one by one in the EER diagram to see the option to change this. I changed all the definitions to "Schema Default" and the problem disappeared. Wow!

Answer 2

Ok I think I figured it out. It seems to be a problem with mySql Workbench, the error disappears if :

• I first create my primary key in salaire_annee_ca,
• Then Forward engineer my database
• Declare my primary key as being a foreign key which references the primary key of salaire_annee
• Forward engineer my database again

Answer 3

The error gets resolved:

To create a Foreign key for a table like this

CREATE TABLE users_so
  (
     username VARCHAR(10) NOT NULL,
     password VARCHAR(32) NOT NULL,
     enabled  SMALLINT,
     PRIMARY KEY (username)
  ); 

The below code works fine

 CREATE TABLE authorities_so
  (
     username  VARCHAR(10) NOT NULL,
     authority VARCHAR(10) NOT NULL,
     FOREIGN KEY (username) REFERENCES users_so(username)
  );  

Answer 4

One of possible cause could be default storage engine on production db.

My problem was similar. I also worked with Workbench, and in local worked perfectly, also worked good in production, until I recreate schema on production server with Forward Engineering.

One of tables become MyISAM instead of InnoDb, and Row format from Don't use become Dynamic.

My solution was:

In Workbench,

• Check every table that requires foreign keys to be InnoDb, and
• Row format to be Default.