Adding foreign key of type char in mysql
char, column-types, foreign-keys, mysql
Solution
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?
Problem
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