Bug #38882 Allow sloppy type matches in FOREIGN KEY constraints

• Submitted: 19 Aug 2008 6:31
• Modified: 13 May 2010 16:12
• Reporter: Susanne Ebrecht
• Status: Verified
• Category: MySQL Server: InnoDB storage engine
• Severity: S4 (Feature request)
• Version: 5.0, 5.1, 6.0
• OS: Any
• Assigned to: Assigned Account
• CPU Architecture: Any

[19 Aug 2008 6:31] Susanne Ebrecht

Description:
ERROR 1005 (HY000): Can't create table 'miracee.t1' (errno: 150)

$ perror 150
MySQL error code 150: Foreign key constraint is incorrectly formed

This happens when the data type from the reference column is "smaller" as the data type from the foreign key column.

For example integer and bigint or float and double. Every integer should fit into a bigint as well the same with float and double.

Here is no reason to forbid that the foreign key column has data type bigint when the reference column just has integer.

This works fine with char and varchar mixes but not with integer/bigint and float/double. 

How to repeat:
create table t(i integer, primary key(i))engine=innodb;
create table t1(i integer, j bigint, primary key(i), foreign key(j) references t(i))engine=innodb;

You can also try:
create table t2(i float, primary key(i))engine=innodb;
create table t3(i integer, j double, primary key(i), foreign key(j) references t2(i))engine=innodb;

Suggested fix:
...

[17 Jul 2018 16:32] miracee miracee

Any news on it?

[17 Jul 2018 16:33] miracee miracee

Also hits MySQL 8