List:General Discussion« Previous MessageNext Message »
From:GF Date:May 16 2012 7:54am
Subject:Foreign key and uppercase / lowercase values
View as plain text  
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
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