List:General Discussion« Previous MessageNext Message »
From:GF Date:June 18 2012 8:09am
Subject:Re: Foreign key and uppercase / lowercase values
View as plain text  
http://bugs.mysql.com/bug.php?id=65648

On Fri, Jun 15, 2012 at 7:00 PM, Rick James <rjames@stripped> wrote:
> You are very close to a standalone test case.  Please create such.  Then
> post it on bugs.mysql.com .
>
>> -----Original Message-----
>> From: GF [mailto:ganfab@stripped]
>> Sent: Friday, June 15, 2012 12:45 AM
>> To: Rick James
>> Cc: Shawn Green; mysql@stripped
>> Subject: Re: Foreign key and uppercase / lowercase values
>>
>> I think the following might give complete information (I removed some
>> columns not involved in the problem)
>>
>> Server version: 5.1.49-3 (Debian)
>>
>> SET collation_connection = utf8_unicode_ci; Query OK, 0 rows affected
>> (0.00 sec)
>>
>>
>>
>> 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)
>>
>> show variables like '%char%';
>> +--------------------------+----------------------------+
>> | Variable_name            | Value    
>                  |
>> +--------------------------+----------------------------+
>> | character_set_client     | utf8          
>             |
>> | character_set_connection | utf8            
>           |
>> | character_set_database   | utf8            
>           |
>> | character_set_filesystem | binary            
>         |
>> | character_set_results    | utf8          
>             |
>> | character_set_server     | utf8          
>             |
>> | character_set_system     | utf8          
>             |
>> | character_sets_dir       | /usr/share/mysql/charsets/ |
>> +--------------------------+----------------------------+
>> 8 rows in set (0.00 sec)
>>
>>
>> select USER_ID from TBL_USER where USER_ID = 'GIUSEPPE';
>> +----------+
>> | USER_ID  |
>> +----------+
>> | GIUSEPPE |
>> +----------+
>> 1 row in set (0.00 sec)
>>
>>  select USER_ID from TBL_USER where USER_ID = 'giuseppe';
>> +----------+
>> | USER_ID  |
>> +----------+
>> | GIUSEPPE |
>> +----------+
>> 1 row in set (0.00 sec)
>>
>>
>> update TBL_USER set USER_ID = LOWER(USER_ID) where USER_ID =
>> 'GIUSEPPE'; ERROR 1451 (23000): Cannot delete or update a parent row: a
>> foreign key constraint fails (`myschema`.`TBL_COMMENT`, CONSTRAINT
>> `FK4F6E52581590B46E` FOREIGN KEY (`USER_ID`) REFERENCES `TBL_USER`
>> (`USER_ID`))
>>
>>
>>
>> | TBL_USER | CREATE TABLE `TBL_USER` (
>>   `USER_ID` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
>>
>>   << cut >>
>>   PRIMARY KEY (`USER_ID`),
>>   << cut >>
>> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
>>
>>
>>
>> | TBL_COMMENT | CREATE TABLE `TBL_COMMENT` (
>>   << cut >>
>>   `ID` bigint(20) NOT NULL AUTO_INCREMENT,
>>   << cut >>
>>   `USER_ID` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
>>   << cut >>
>>   PRIMARY KEY (`ID`),
>>   << cut >>
>>   KEY `FK4F6E52581590B46E` (`USER_ID`),
>>   << cut >>
>>   CONSTRAINT `FK4F6E52581590B46E` FOREIGN KEY (`USER_ID`) REFERENCES
>> `TBL_USER` (`USER_ID`),
>>   << cut >>
>> ) ENGINE=InnoDB AUTO_INCREMENT=7876 DEFAULT CHARSET=utf8
>> COLLATE=utf8_unicode_ci |
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