Good morning, 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 "case insensitive". 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 the update???) 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) Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
