From: Rick James
Date: June 15 2012 5:00pm
Subject: RE: Foreign key and uppercase / lowercase values
List-Archive: http://lists.mysql.com/mysql/227677
Message-Id: <2E7DD7ADE53B044C8C8BCD9C5829E1EB1487607F23@SP2-EX07VS01.ds.corp.yahoo.com>
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

You are very close to a standalone test case.  Please create such.  Then po=
st 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
>=20
> I think the following might give complete information (I removed some
> columns not involved in the problem)
>=20
> Server version: 5.1.49-3 (Debian)
>=20
> SET collation_connection =3D utf8_unicode_ci; Query OK, 0 rows affected
> (0.00 sec)
>=20
>=20
>=20
> 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)
>=20
> 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)
>=20
>=20
> select USER_ID from TBL_USER where USER_ID =3D 'GIUSEPPE';
> +----------+
> | USER_ID  |
> +----------+
> | GIUSEPPE |
> +----------+
> 1 row in set (0.00 sec)
>=20
>  select USER_ID from TBL_USER where USER_ID =3D 'giuseppe';
> +----------+
> | USER_ID  |
> +----------+
> | GIUSEPPE |
> +----------+
> 1 row in set (0.00 sec)
>=20
>=20
> update TBL_USER set USER_ID =3D LOWER(USER_ID) where USER_ID =3D
> '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`))
>=20
>=20
>=20
> | TBL_USER | CREATE TABLE `TBL_USER` (
>   `USER_ID` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
>=20
>   << cut >>
>   PRIMARY KEY (`USER_ID`),
>   << cut >>
> ) ENGINE=3DInnoDB DEFAULT CHARSET=3Dutf8 COLLATE=3Dutf8_unicode_ci |
>=20
>=20
>=20
> | 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=3DInnoDB AUTO_INCREMENT=3D7876 DEFAULT CHARSET=3Dutf8
> COLLATE=3Dutf8_unicode_ci |