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
May 16
Ananda Kumar Re: Foreign key and uppercase / lowercase values
May 16, 2012; 16:12
Ananda Kumar
Re: Foreign key and uppercase / lowercase values
May 16
Rick James RE: Foreign key and uppercase / lowercase values
May 16, 2012; 08:27
Rick James
RE: Foreign key and uppercase / lowercase values
May 16
Shawn Green Re: Foreign key and uppercase / lowercase values
May 16, 2012; 11:35
Shawn Green
Re: Foreign key and uppercase / lowercase values
Jun 12
GF Re: Foreign key and uppercase / lowercase values
Jun 12, 2012; 15:20
GF
Re: Foreign key and uppercase / lowercase values
Jun 13
Rick James RE: Foreign key and uppercase / lowercase values
Jun 13, 2012; 16:40
Rick James
RE: Foreign key and uppercase / lowercase values
Jun 15
GF Re: Foreign key and uppercase / lowercase values
Jun 15, 2012; 09:44
GF
Re: Foreign key and uppercase / lowercase values
Jun 15
Rick James RE: Foreign key and uppercase / lowercase values
Jun 15, 2012; 10:00
Rick James
RE: Foreign key and uppercase / lowercase values
Jun 15
Shawn Green Re: Foreign key and uppercase / lowercase values
Jun 15, 2012; 15:05
Shawn Green
Re: Foreign key and uppercase / lowercase values
Jun 15
Rick James RE: Foreign key and uppercase / lowercase values
Jun 15, 2012; 12:19
Rick James
RE: Foreign key and uppercase / lowercase values
Jun 15
Shawn Green Re: Foreign key and uppercase / lowercase values
Jun 15, 2012; 16:40
Shawn Green
Re: Foreign key and uppercase / lowercase values
Jun 16
Walter Tross Re: Foreign key and uppercase / lowercase values
Jun 16, 2012; 00:34
Walter Tross
Re: Foreign key and uppercase / lowercase values
Jun 18
GF Re: Foreign key and uppercase / lowercase values
Jun 18, 2012; 10:09
GF
Re: Foreign key and uppercase / lowercase values
Search
Lasso Programming
This site manages and broadcasts several email lists pertaining to Lasso Programming and technologies related and used by Lasso developers. Sign up today!