List:General Discussion« Previous MessageNext Message »
From:Rick James Date:June 13 2012 11:40pm
Subject:RE: Foreign key and uppercase / lowercase values
View as plain text  
To discuss this further, please provide SHOW CREATE TABLE for the table in question and
the table(s) tied to it via FOREIGN KEYs.

> -----Original Message-----
> From: GF [mailto:ganfab@stripped]
> Sent: Tuesday, June 12, 2012 6:20 AM
> To: Shawn Green
> Cc: mysql@stripped
> Subject: Re: Foreign key and uppercase / lowercase values
> 
> Good morning.
> The application is Java.
> The database version is : Server version: 5.1.49-3 (Debian)
> 
> This is an example of the problem:
> ______________________________
> mysql> SET collation_connection = utf8_unicode_ci;
> Query OK, 0 rows affected (0.00 sec)
> 
> mysql> show variables like '%colla%';
> +----------------------+-----------------+
> | Variable_name | Value |
> +----------------------+-----------------+
> | collation_connection | utf8_unicode_ci | collation_database |
> | utf8_unicode_ci | collation_server | utf8_unicode_ci |
> +----------------------+-----------------+
> 3 rows in set (0.00 sec)
> 
> mysql> update MY_TABLE set USER_ID = LOWER(USER_ID) where USER_ID =
> mysql> 'XXYYZZ';
> ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key
> constraint fails etc. etc.
> ______________________________
> 
> Since the used collation is "_ci" (I suppose it means case
> insensitive) I don't understand why it is giving an error trying to
> change a value to lowercase.
> 
> I did try on a test environment to use the trick (SET
> foreign_key_checks=0;) but I don't understand why I should disable the
> foreign key checks when I am NOT violating them.
> 
> The application was able to write in some other tables the USER_ID in
> lowercase. And I think that was an expected behaviour because the
> collation is case insensitive!
> Why now I can't set some values from uppercase to lowercase?
> 
> There is not any weird character in the USER_ID column, just from A to
> Z.
> 
> Thank you.
> 
> On Wed, May 16, 2012 at 5:35 PM, Shawn Green <shawn.l.green@stripped>
> wrote:
> > Hello Ananda,
> >
> >
> > On 5/16/2012 6:42 AM, Ananda Kumar wrote:
> >>
> >> why are not using any where condition in the update statment
> >>
> >
> > WHERE clauses are not required. Performing a command without one will
> > affect ever row on the table.
> >
> >> 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.
> >>> ...
> >>>
> >>> Have you any idea how to solve this situation without
> >>> stopping/recreating the DB? (it's a production environment) Thanks
> >>>
> >
> > Have you tried ?
> >
> >  SET foreign_key_checks=0;
> > http://dev.mysql.com/doc/refman/5.5/en/server-system-
> variables.html#sy
> > svar_foreign_key_checks
> >
> > If that does not work, you would need to first un-create your Foreign
> > Key relationships, update your key values (the USER_ID fields), then
> > re-create your Foreign Key relationships.
> >
> > Regards,
> > --
> > Shawn Green
> > MySQL Principal Technical Support Engineer Oracle USA, Inc. -
> Hardware
> > and Software, Engineered to Work Together.
> > Office: Blountville, TN
> >
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql To unsubscribe:
> > http://lists.mysql.com/mysql
> >
> 
> --
> 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