MySQL Bugs: #75620: Foreign key allows insert or update on child table but gets Error 1451 on parent

Bug #75620	Foreign key allows insert or update on child table but gets Error 1451 on parent
Submitted:	24 Jan 2015 15:42	Modified:	26 Jan 2015 14:16
Reporter:	Robert Simpson	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: InnoDB Plugin storage engine	Severity:	S3 (Non-critical)
Version:	5.6.22	OS:	Linux (CentOS 7)
Assigned to:		CPU Architecture:	Any
Tags:	foreign key update error 1451 case sensitive

Description:
On a table where a column is case insensitive (by default), child row can be created that violates the foreign key in a case-sensitive manner, but parent row cannot be updated to match the case of the child row.

See Minimal Test Case below.

Expected results - either:

1) If foreign key is case sensitive - insert of child row should fail.

2) If a row is being updated such that the value of the child column matches the value of the parent column exactly (including case-sensitivity), the update should not fail because the "foreign key constraint fails".

How to repeat:
mysql> create table t1 (c1 varchar(2), key (c1));
Query OK, 0 rows affected (0.00 sec)

mysql> create table t2 (c2 varchar(2), constraint c2c1 foreign key (c2) references t1(c1));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values('ab');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t2 values('Ab');
Query OK, 1 row affected (0.00 sec)

mysql> update t1 set c1 = 'Ab';
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`apps`.`t2`, CONSTRAINT `c2c1` FOREIGN KEY (`c2`) REFERENCES `t1` (`c1`))

Suggested fix:
Depends on whether the foreign key is case sensitive or case-insensitive.

In a slightly longer test case, if the child row is originally inserted with the value in all lower case, it cam be updated to mixed case successfully but then the parent update fails in the same manner.

This has nothing to do with Foreign Key constraint being case sensitive or case insensitive. 

Actually, this is a behavior that follows SQL standard and has the same result whenever a change is attempted on the tuple in the parent table, which is referenced by any tuple in the child table.

Our manual, actually, explains it quite clearly:

"
The MySQL Server rejects the delete or update operation for the parent table if there is a related foreign key value in the referenced table. Some database systems have deferred checks, and NO ACTION is a deferred check. In MySQL, foreign key constraints are checked immediately, so NO ACTION is the same as RESTRICT.

"