Adding foreign key of type char in mysql

Question

i got a problem adding a foreign key in mysql (using phpmyadmin).

ALTER TABLE `production_x_country` ADD  FOREIGN KEY (`country`) REFERENCES `pmdb_0.3.12`.`countries`(`iso_3166_1`) ON DELETE CASCADE ON UPDATE CASCADE;

#1215 - Cannot add foreign key constraint

based on some research and tests i've come to the conclusion that CHAR (that production_x_country.country field) is no valid foreign key field type - though i did not find any hint to that assumption in the mysql docs. if i change the column type to some other character type like VARCHAR, the procedure works.

a similar question was "solved" here, but that linked answer wasn't about the type-problem but about a country code being a primary key (what makes perfect sense to me): https://stackoverflow.com/a/1419235/4302731

---

table descriptions:

CREATE TABLE IF NOT EXISTS `countries` (
  `iso_3166_1` char(3) NOT NULL,  <----- primary key to be referenced to
  `name` varchar(200) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `countries` ADD PRIMARY KEY (`iso_3166_1`);



CREATE TABLE IF NOT EXISTS `production_x_country` (
  `production` int(11) NOT NULL,
  `country` char(3) CHARACTER SET utf8 NOT NULL   <----- column that should hold the foreign key
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

---

is there any solution (yes, i could go on using varchar, but thats not satisfying to me)? and most important: is there any explanation?

thank you for your help!

---

solved - see my own answer below

Answer 1

solved! this is not about the char field type but about the collation!

i like using UTF-8 mostly utf8_bin. so did i with the collation of my primary key:

`iso_3166_1` char(3) CHARACTER SET utf8

once i changed the primary key's collation to "latin1_swedish_ci" the foreign key application worked. leads to the question: why is the collation of the primary key field (maybe also the foreign key field's, haven't checked that yet) important?