Error 1215: Cannot add foreign key constraint

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!

回答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!

回答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

回答3:

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.

回答4:

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)
  );