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