List:General Discussion« Previous MessageNext Message »
From:Rick James Date:May 16 2012 3:27pm
Subject:RE: Foreign key and uppercase / lowercase values
View as plain text  
What language(s) are involved?  What collation are you using now?
The German sharp-S does not work well with case folding.
utf8_unicode_ci could have stuff folding together.
Etc.

If you can find a USER_ID that is causing trouble, please provide
   SELECT HEX(USER_ID) FROM ... WHERE ...
for further discussion.

Instead of changing the data, why not do the casefolding as you SELECT into Java?

> -----Original Message-----
> From: Ananda Kumar [mailto:anandkl@stripped]
> Sent: Wednesday, May 16, 2012 3:43 AM
> To: GF
> Cc: mysql@stripped
> Subject: Re: Foreign key and uppercase / lowercase values
> 
> why are not using any where condition in the update statment
> 
> On Wed, May 16, 2012 at 1:24 PM, GF <ganfab@stripped> wrote:
> 
> > 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
> >
> >
Thread
Foreign key and uppercase / lowercase valuesGF16 May
  • Re: Foreign key and uppercase / lowercase valuesAnanda Kumar16 May
    • RE: Foreign key and uppercase / lowercase valuesRick James16 May
    • Re: Foreign key and uppercase / lowercase valuesShawn Green16 May
      • Re: Foreign key and uppercase / lowercase valuesGF12 Jun
        • RE: Foreign key and uppercase / lowercase valuesRick James13 Jun
          • Re: Foreign key and uppercase / lowercase valuesGF15 Jun
            • RE: Foreign key and uppercase / lowercase valuesRick James15 Jun
              • Re: Foreign key and uppercase / lowercase valuesShawn Green15 Jun
                • RE: Foreign key and uppercase / lowercase valuesRick James15 Jun
                  • Re: Foreign key and uppercase / lowercase valuesShawn Green15 Jun
                    • Re: Foreign key and uppercase / lowercase valuesWalter Tross15 Jun
              • Re: Foreign key and uppercase / lowercase valuesGF18 Jun