mysql char 添加内容,在mysql中添加char类型的外键

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): http://stackoverflow.com/a/1419235/4302731

table descriptions:

CREATE TABLE IF NOT EXISTS `countries` (

`iso_3166_1` char(3) NOT NULL,

`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

) 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

解决方案

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?