List:General Discussion« Previous MessageNext Message »
From:GF Date:June 12 2012 1:20pm
Subject:Re: Foreign key and uppercase / lowercase values
View as plain text  
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 = '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#sysvar_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
>
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