I have an application where the user ids were stored lowercase.
Some batch import, in the user table some users stored a uppercase
id, and for some applicative logic, in other tables that have a
foreign key to the user table, their user ids are stored lowercase.
MySQL didn't throw any error probalby because the collation used is
My problem is that the application is Java and java strings are case
sensitive, so now I want to set user ids to lowercase EVERYWHERE.
I supposed that I could execute with ease these commands:
- update mytable1 set USER_ID = LOWER(USER_ID);
- update mytable2 set USER_ID = LOWER(USER_ID);
- update mytable3 set USER_ID = LOWER(USER_ID);
But for some tables I got some Foreign key constraint to throw an
error. (butwhy they didn't throw an error on the insert but just on
And if I try to disable foreign key checks during these updates, I get
some "duplicate key" errors where USER_ID is a part of composite key
with other columns. (but I don't have any data that might cause a real
duplicate key error just changing the case of one column)
Have you any idea how to solve this situation without
stopping/recreating the DB? (it's a production environment)