A special restriction on the foreign keys of the decimal type of MySQL

First, the problem description

In Oracle, PostgreSQL is running the HIBENATE/JPA application, while switching to MySQL while inserting the data times wrong: "Mysqlintegrityconstraintviolationexception:cannot Add or update a child row:a FOREIGN KEY constraint fails ... ".

Check the application code, did not find the wrong, online direct search, also found no valuable clues.

Back to MySQL, first delete the error when the foreign key, start the application again, insert data successfully. Then when you want to add a foreign key, the same error message appears. It seems that the problem is not the application, but on MySQL side.

After locking the range to search the Internet, the reason is soon determined: because the child table's association key is decimal (15), and the parent table's primary key is a decimal (17). The scope of the parent table's primary key is outside the scope of the child table key.

The type of the associated key of the table is changed to decimal (17), the foreign key is successful; then the application's Insert data function, all ok!

Second, further testing

Then go through the MySQL official document and find that the foreign key has such restrictions:

Corresponding columns in the foreign key and the referenced key must has similar data types. The size and sign of the integer types mustis the same. The length of string types need not being the same. For Nonbinary (character) string columns, the character set and collation must is the same.

This refers only to the integer, string, which does not refer to the decimal type, and then makes a test. The length of the association key of the sub-table in this example is changed to decimal (18) and is found to be normal. Description for the decimal type, the type of the child Table Association key can be a superset of the parent table's primary key, preferably exactly the same.

three, The Spit Groove

At the end of the essay, I couldn't help but want to vomit:

• Oracle, PostgreSQL is tolerated in this case, why doesn't MySQL tolerate it?
• If you don't tolerate it, why not make an error (at least a warning) while creating the foreign key of the empty table, rather than wait until the runtime comes out?
• Finally spit out the official document, if not happen to encounter, estimated lifetime is not aware of the decimal type of foreign keys have this limitation.

A special restriction on the foreign keys of the decimal type of MySQL