MySQL utf8mb4 Foreign Key Error

Question

A few days ago we migrated a database from utf8 to utf8mb4. Due to the migration most of our primary keys were updated from VARCHAR(255) to VARCHAR(191) due to the additional bytes per character.

The problem is that now we can't add new FOREIGN keys on new tables referencing the existent ones. We get the following error in MySQL Workbench:

"Referenced table has no candidate columns with a compatible type for table.id. Hint: source column has type VARCHAR(191), PK of referenced table is VARCHAR(191)."

If we try to add the foreign key through the console we get:

LATEST FOREIGN KEY ERROR

2014-02-13 10:27:51 126bb3000 Error in foreign key constraint of table table/#sql-159_2b2: foreign key (fk_id) references table (id): Cannot find an index in the referenced table where the referenced columns appear as the first columns, or column types in the table and the referenced table do not match for constraint.

However both types are defined as VARCHAR(191) as you can see in the reported error.

What are we missing?

Answer 1

Found the issue and the solution. It seems that MYSQL has some issues while comparing within collations when using utf8mb4.

Assume that your MYSQL server is using utf8mb4 as the default collation, this means that any new table will inherit that collation as their default.

If we want to create a new Table, let's called it A, Table A has a collation of utf8mb4 - default collation (inherit from the DB default). If you want to create a foreign key from column p in A (which will have as collation 'Table Default' which should be 'utf8mb4 - default collation) to column x of Table B where B has the same collation: utf8mb4 - default collation but column x has a collation of utf8mb4_unicode_ci you will get the error in the question:

Referenced table has no candidate columns with a compatible type for table.id. Hint: source column has type VARCHAR(191), PK of referenced table is VARCHAR(191).

So, if you have the indexes and the types are the same, the error then is because of a mismatch in the columns collation.

To fix it I had to explicitly alter column p to use the same collation as the referenced column:

ALTER TABLE `database`.`A` CHARACTER SET = utf8mb4 ;
ALTER TABLE `database`.`A` CHANGE COLUMN `p` `p` VARCHAR(191) CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_ci' NOT NULL  

and then adding the foreign key works like a charm:

ALTER TABLE `database`.`A` 
ADD CONSTRAINT `FKD84ACC0C2200B55`
FOREIGN KEY (`p` )
REFERENCES `database`.`B` (`x` )
ON DELETE CASCADE
ON UPDATE CASCADE;