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:
Category:MySQL Server: Documentation Severity:S4 (Feature request)
Version:8.0 OS:Any
Assigned to: CPU Architecture:Any
Tags: compatibility, constraint, Data Type, decimal

[29 Nov 2020 3:35] Kasey Chang
On MySQL 8.0 Ref Man, foreign key restrictions, 


>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:

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.
[1 Dec 2020 15:01] MySQL Verification Team
Hi Mr. Chang,

Thank you for your documentation feature request.

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

Verified as reported.
[1 Dec 2020 22:05] Kasey Chang
Thank you. Inspired by this topic on Reddit when I was trying to solve someone else's problem. 

[9 Dec 2020 3:20] Paul DuBois
Posted by developer:
Updated as suggested. Thanks.
[9 Dec 2020 13:54] MySQL Verification Team
Thank you, Paul ......