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://mcmap.net/q/1131919/-is-using-char-as-a-primary-foreign-key-a-no-no
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