0

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?

Mohammad Edris Raufi
  • 1,393
  • 1
  • 13
  • 34
thopaw
  • 3,796
  • 2
  • 17
  • 24

1 Answers1

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.

Rinze Smits
  • 810
  • 6
  • 5
  • Upvoted — but there's a caveat to this method: changing collation risks making some strings in the foreign key no longer match the strings they reference, but you won't know this is happening if you `set foreign_key_checks=0`. Enabling foreign_key_checks=1 does not re-evaluate all the data. – Bill Karwin Feb 13 '23 at 21:59
  • When I can loose a relation as the keys do not equals anymore after the conversion, this is not a solution for me. Are there any experience what is the probability that this happens when converting from utf8mb4_unicode_ci to utf8mb4_de_pb_0900_as_ci? – thopaw Feb 14 '23 at 06:40