MySQL Bugs: #101792: Unclear limitations to datatype compatibility

Bug #101792	Unclear limitations to datatype compatibility
Submitted:	29 Nov 2020 3:35	Modified:	9 Dec 2020 3:20
Reporter:	Kasey Chang	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S4 (Feature request)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	compatibility, constraint, Data Type, decimal

Description:
On MySQL 8.0 Ref Man, foreign key restrictions, 

https://dev.mysql.com/doc/refman/8.0/en/create-table-foreign-keys.html#foreign-key-restric...

>Corresponding columns in the foreign key and the referenced key must have similar data types. The size and sign of integer types must be the same.

However, it was omitted that DECIMAL type fields must also have the same size. Apparently all "fixed precision" type such as DECIMAL or INTEGER and their subtypes are subject to this foreign key data type restriction. 

How to repeat:
https://dev.mysql.com/doc/refman/8.0/en/create-table-foreign-keys.html#foreign-key-restric...

Try declare a table A with field of type DECIMAL(20) then a table B with field of type DECIMAL(10). Then try to set a constraint of one to the other. They will not work. If they are the same size (both DECIMAL (10)) then it would work. 

The wording of the constraint does not cover data type decimal, which are NOT integers (but fixed precision numbers)

Suggested fix:
simply adding "fixed (precision) numeric data type such as DECIMAL should do nicely.

Hi Mr. Chang,

Thank you for your documentation feature request.

This is a very small one, but it is justified.

Verified as reported.

Thank you. Inspired by this topic on Reddit when I was trying to solve someone else's problem. 

https://www.reddit.com/r/learnprogramming/comments/k2vkiw/mysql_foreign_key_constraint_fai...

Posted by developer:
 
Updated as suggested. Thanks.

Thank you, Paul ......