title: Mysql: using two foreign keys to the same table
body: <p>I'm using MySQL workbench to design a database. Server is mysql 5.5.6</p>
  
  <p>I've defined a few foreign keys linking the "candidates" table to the "countries" table. I get this error:</p>
  
  <pre><code>Executing SQL script in server
  ERROR: Error 1005: Can't create table 'customer.candidats' (errno: 150)
  </code></pre>
  
  <p>The thing is: i'm referencing twice the countries table: once for the "nationality" column, once for the user address's country of origin. Is that allowed? Is this the right way to do it?</p>
  
  <p>Here is the generated code that seems to trigger the issue.</p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS `customer`.`candidats` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `nom` VARCHAR(40) NULL,
    `prenom` VARCHAR(40) NULL,
    `qualite` ENUM('0001','0002') NULL COMMENT '0001 = Madame\n0002 = Monsieur',
    `sexe` SET('1','2') NULL COMMENT '1 = Femme\n2 = Homme',
    `date_de_naissance` DATE NULL,
    `Nationalite` INT NOT NULL,
    `selor_bilinguisme` TINYINT(1) NULL,
    `rue` VARCHAR(60) NULL,
    `numero` VARCHAR(10) NULL,
    `pays` INT NOT NULL,
    `region` INT NOT NULL,
    `localité` VARCHAR(40) NULL,
    `code_postal` VARCHAR(10) NULL,
    `email` VARCHAR(241) NULL,
    `tel_domicile` VARCHAR(30) NULL,
    `tel_bureau` VARCHAR(30) NULL,
    `tel_mobile` VARCHAR(30) NULL,
    `tel_prefere` ENUM('01','02','03') NULL DEFAULT '03',
    PRIMARY KEY (`id`),
    INDEX `fk_candidats_pays_idx` (`Nationalite` ASC, `pays` ASC),
    INDEX `fk_candidats_régions1_idx` (`region` ASC),
    CONSTRAINT `fk_candidats_pays`
      FOREIGN KEY (`Nationalite` , `pays`)
      REFERENCES `customer`.`pays` (`id` , `id`)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION,
    CONSTRAINT `fk_candidats_régions1`
      FOREIGN KEY (`region`)
      REFERENCES `customer`.`régions` (`id`)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION)
  ENGINE = InnoDB
  </code></pre>
  
  <p>The "pays" table ("countries" in French)</p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS `customer`.`pays` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `nom_fr` VARCHAR(45) NULL,
    `nom_nl` VARCHAR(45) NULL,
    `nationalite_fr` VARCHAR(45) NULL,
    `nationalite_nl` VARCHAR(45) NULL,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `id_UNIQUE` (`id` ASC))
  ENGINE = InnoDB
  </code></pre>