Mysql - convert Tables in Database to diffrent encoding and collate - foreign key constraints are failing

Question

I have a MySQL Database with the Charset utfmb4 and the collate utf8mb4_unicode_ci.

No, I noticed that this influences my search queries where I use like '%grün%'. This would also match 'Grund'.

I found that this behavior is because of the charset and collate of my Tables/Columns. Now I want to switch the tables to the collate utf8mb4_de_pb_0900_ai_ci to avoid the wrong selection of german umlaute.

So first I change the default settings for my database which is accepted

ALTER DATABASE CHARACTER SET utf8mb4 COLLATE utf8mb4_de_pb_0900_ai_ci;

Setting the default setting for my first table is also accepted

ALTER TABLE tablename CHARACTER SET utf8mb4 COLLATE utf8mb4_de_pb_0900_ai_ci;

But when I want to convert the existing data to the new settings I get an error

ALTER TABLE tablename  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_de_pb_0900_ai_ci;

Referencing column 'column1' and referenced column 'id' in foreign key constraint 'contraintname_fkey' are incompatible.

I can do this with every table and always get the error that the constraint is not compatible as the foreign table is not converted.

I found clever Queries to generate all alter statements, but I can not execute them because of the error described above.

Is there an easy way to do this?

Answer 1

You can disable checking of foreign keys while you are altering your tables.

SET FOREIGN_KEY_CHECKS=0;
...Your ALTER TABLE queries...
SET FOREIGN_KEY_CHECKS=1;

Remember that AI in the collation means Accent Insensitive, meaning accents are not taken into account when comparing text. For a collation that is sensitive to accents use a collation with _AS_ in its name.