title: Error 1215 on adding foreign key. Help me with what I haven't tried
body: <p>I'm trying to set a foreign key relationship between two existing table but for some reason which I'm missing I keep getting this error 1215.</p>
  
  <p>The relevant fields of the two tables are:</p>
  
  <pre><code>CREATE TABLE `approvals` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `id_contract` int(11) NOT NULL,
    `id_user` int(11) NOT NULL,
    `lang` varchar(8) NOT NULL,
    `request_ts` datetime NOT NULL,
    `version` int(11) NOT NULL,
    -- more nullable columns here with no indexing/relationships
    PRIMARY KEY (`id`),
    KEY `approval_id_user` (`id_user`),
    KEY `version` (`version`,`lang`,`id_contract`),
    CONSTRAINT `approval_id_user` FOREIGN KEY (`id_user`) 
        REFERENCES `users` (`iduser`) ON DELETE CASCADE ON UPDATE CASCADE
  ) ENGINE=InnoDB AUTO_INCREMENT=1079 DEFAULT CHARSET=utf8
  </code></pre>
  
  <pre><code>CREATE TABLE `version` (
    `idversion` int(11) NOT NULL,
    `idcontract` int(11) NOT NULL,
    `language` varchar(8) NOT NULL,
    PRIMARY KEY (`idversion`,`idcontract`,`language`),
    KEY `fk_version_contracts_idx` (`idcontract`),
    CONSTRAINT `fk_kidversion_contracts` FOREIGN KEY (`idcontract`) 
        REFERENCES `contracts` (`id_contract`) 
        ON DELETE NO ACTION ON UPDATE NO ACTION
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8
  </code></pre>
  
  <p><strong>What have I tried</strong><br>
  First thing I checked is the persistency:</p>
  
  <pre><code>SELECT * FROM approvals a 
  WHERE NOT EXISTS (
      SELECT 1 FROM version v 
      WHERE v.idcontract = a.id_contract AND v.language = a.lang 
          AND v.idversion = a.version);
  </code></pre>
  
  <p>which resulted in 0 rows returned out of 701, ok my db should be good from the data pov.</p>
  
  <p>So I checked the collation and for some reason the version table was latin1 vs utf8_generic_ci, ok so I restored the version table to utf8 with this.</p>
  
  <pre><code>ALTER TABLE `kidversion` 
  COLLATE = utf8_general_ci;
  
  -- and for good measure
  ALTER TABLE `kidversion` 
  CHANGE COLUMN `language` `language` VARCHAR(8) 
      CHARACTER SET 'utf8' COLLATE 'utf8_general_ci' NOT NULL;
  </code></pre>
  
  <p>But at this point I'm still getting an error while trying to create the foreign key:</p>
  
  <pre><code>ALTER TABLE `approvals` 
  ADD CONSTRAINT `approval_ibfk_1`
    FOREIGN KEY (`version` , `lang` , `id_contract`)
    REFERENCES `version` (`idversion` , `language` , `idcontract`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION;
  </code></pre>
  
  <p>And that results in a 1215 in every step I made. Surely I'm missing something.. can anyone help me out with this?</p>