title: Error 1822: Failed to add the foreign key constaint. Missing index for constraint
body: <p>I found some threads about the error. But all the solutions does not work for me.</p>
  
  <p>My system already had the following tables:</p>
  
  <pre><code>CREATE TABLE `bdo2_agencia` (
    `cod_uf` char(2) NOT NULL,
    `cod_agencia` char(9) NOT NULL,
    `nome` varchar(100) NOT NULL,
    PRIMARY KEY (`cod_uf`,`cod_agencia`),
    KEY `fk_agencia_2_uf_idx` (`cod_uf`),
    CONSTRAINT `fk_agencia_2_uf` FOREIGN KEY (`cod_uf`) REFERENCES `bdo2_uf` (`cod_uf`) ON DELETE NO ACTION ON UPDATE NO ACTION
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
  
  CREATE TABLE `bdo2_login` (
    `login` char(30) NOT NULL,
    `ativo` tinyint(1) DEFAULT '1' COMMENT 'informa se o login está ativo',
    PRIMARY KEY (`login`),
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  </code></pre>
  
  <p>I tried to create another table between them, defining a relationship N:M</p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS `bdo2`.`bdo2_login_agencia` (
    `cod_uf` CHAR(2) NOT NULL,
    `cod_agencia` CHAR(9) NOT NULL,
    `login` CHAR(30) NOT NULL,
    PRIMARY KEY (`cod_uf`, `cod_agencia`, `login`),
    INDEX `fk_login_2_login_agencia_idx` (`login` ASC),
    INDEX `fk_agencia_2_login_agencia_idx` (`cod_uf` ASC, `cod_agencia` ASC),
    CONSTRAINT `fk_agencia_2_login_agencia`
    FOREIGN KEY (`cod_uf` , `cod_agencia`)
    REFERENCES `bdo2`.`bdo2_agencia` (`cod_uf` , `cod_agencia`) ON DELETE NO ACTION ON UPDATE NO ACTION,
    CONSTRAINT `fk_login_2_login_agencia`
    FOREIGN KEY (`login`) REFERENCES `bdo2`.`bdo2_login` (`login`) ON DELETE NO ACTION ON UPDATE NO ACTION)
  ENGINE = InnoDB;
  </code></pre>
  
  <p>but I received the following error:</p>
  
  <pre><code>Error Code: 1215. Cannot add foreign key constraint
  </code></pre>
  
  <p>With the command</p>
  
  <pre><code>SHOW ENGINE innodb STATUS
  </code></pre>
  
  <p>I got the following message:</p>
  
  <pre><code>------------------------
  LATEST FOREIGN KEY ERROR
  ------------------------
  2016-03-22 10:14:05 7fe09c49f700 Error in foreign key constraint of table bdo2/bdo2_login_agencia:
  
  FOREIGN KEY (`cod_uf` , `cod_agencia`)
  REFERENCES `bdo2`.`bdo2_agencia` (`cod_uf` , `cod_agencia`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_login_2_login_agencia`
  FOREIGN KEY (`login`) REFERENCES `bdo2`.`bdo2_login` (`login`)
  ON DELETE NO ACTION ON UPDATE NO ACTION)
  ENGINE = InnoDB:
  
  Cannot find an index in the referenced table where the referenced
  columns appear as the first columns, or column types
  in the table and the referenced table do not match for constraint.
  Note that the internal storage type of ENUM and SET changed in
  tables created with &gt;= InnoDB-4.1.12, and such columns in old tables
  cannot be referenced by such columns in new tables.
  See http://dev.mysql.com/doc/refman/5.6/en/innodb-foreign-key-constraints.html
  for correct foreign key definition.
  </code></pre>
  
  <p>So I created the table without the constraints and I tried to create the constraints individually</p>
  
  <pre><code>ALTER TABLE `bdo2`.`bdo2_login_agencia` 
  ADD INDEX `fk_agencia_2_login_agencia_idx` (`cod_uf` ASC, `cod_agencia` ASC),
  ADD INDEX `fk_login_2_login_agencia_idx` (`login` ASC);
  ALTER TABLE `bdo2`.`bdo2_login_agencia` 
  ADD CONSTRAINT `fk_agencia_2_login_agencia`
  FOREIGN KEY (`cod_uf` , `cod_agencia`)
  REFERENCES `bdo2`.`bdo2_agencia` (`cod_uf` , `cod_agencia`)
  ON DELETE NO ACTION ON UPDATE NO ACTION,
  ADD CONSTRAINT `fk_login_2_login_agencia`
  FOREIGN KEY (`login`)
  REFERENCES `bdo2`.`bdo2_login` (`login`)
  ON DELETE NO ACTION ON UPDATE NO ACTION;
  </code></pre>
  
  <p>but received the following error:</p>
  
  <pre><code>ERROR 1822: Failed to add the foreign key constaint. Missing index for constraint 'fk_agencia_2_login_agencia' in the referenced table 'bdo2_agencia'
  </code></pre>
  
  <p>If index exists, why returns this message?
  It would be a bug of version? I tested in two servers (versions 5.6.23 and 5.6.29) on Linux, both presented the same problem.</p>