MySQL Bugs: #19704: Foreign key doesn't work on UCS2 column

Bug #19704	Foreign key doesn't work on UCS2 column
Submitted:	11 May 2006 3:58	Modified:	19 Jun 2006 7:18
Reporter:	Richard Schulman	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	5.0.21	OS:	Windows (XP)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
Foreign key integrity doesn't work when defined on char columns in databases using a UCS2 character set.

How to repeat:
In an InnoDB database using the ucs2 character set, create a parent and child table, each of which has as its primary key a non-null char(1) column. Define a foreign key on the child's primary key column that references the primary key of the parent. Insert a parent record, then attempt to insert a child record with the same primary key as the parent. The insert should be successful, but instead one gets a message "Cannot add or update a child row -- Error #1452."

Thank you for a problem report. Please, provide the exact SQL statements to demonstrate the problem instead of text description that can be misinterpreted.

Marko,

please look at this UCS2 problem.

Regards,

Heikki

Richard,
I cannot repeat this with the information you provided:

mysql> create table p(a char(1))engine=innodb default charset=ucs2;
Query OK, 0 rows affected (0.41 sec)

mysql> drop table p;
Query OK, 0 rows affected (0.01 sec)

mysql> create table p(a char(1) primary key)engine=innodb default charset=ucs2;
Query OK, 0 rows affected (0.04 sec)

mysql> create table c(a char(1) primary key,constraint c foreign key(a)references p(a))engine=innodb default charset=ucs2;
Query OK, 0 rows affected (0.05 sec)

mysql> insert into p values(0x0120),(0x0020),(0x2020),(0x0041);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> insert into c values(0x0120),(0x0020),(0x2020),(0x0041);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0
mysql> delete from p;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test/c`, CONSTRAINT `c` FOREIGN KEY (`a`) REFERENCES `p` (`a`))
mysql> delete from c;
Query OK, 4 rows affected (0.00 sec)

mysql> delete from p;
Query OK, 4 rows affected (0.00 sec)

Please post the exact commands that you used.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".