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:gan...@gmail.com] > Sent: Tuesday, June 12, 2012 6:20 AM > To: Shawn Green > Cc: mysql@lists.mysql.com > 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.gr...@oracle.com> > 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<gan...@gmail.com> 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
