FOREIGN key DDL is functioning correctly in Oracle, in MySQL report exception and solution

Remember a MySQL foreign KEY constraint design flaw

Background information

Recently in the database migration of the project, from Oracle to MySQL, a foreign key constraint was running normally in Oracle, and the MySQL reported an exception. (because it took a few days to take over, not familiar with the business and framework, it took a lot of time to deal with the problem.) ）

[2018-08-01 13:34:19] [23000] [1452] Cannot add or update a child row:a FOREIGN KEY constraint fails (' Bov '. ' Pro_sites_brandrequest ', constraint ' AA ' F Oreign KEY (' id ') REFERENCES ' pro_sites_setuprequest ' (' id ') on DELETE CASCADE)

Oracle's DDL

drop table Models; CREATE table Models (  modelid number (6)  PRIMARY KEY,  Name VARCHAR ());d ROP table Orders; CREATE TABLE Orders (  modelid number     (8) PRIMARY key,  Description VARCHAR (+),  FOREIGN key (ModelID) REFERENCES Models (ModelID) on the  DELETE Cascade); INSERT into Models (ModelID, Name) VALUES (1, ' model '); INSERT INTO Order S (modelid,description) VALUES (1, ' order ');

SELECT * FROM Models;1    modelselect * from orders;1    order

MySQL's DDL

drop table Models; CREATE table Models (  modelid Decimal (6,0)  PRIMARY KEY,  Name VARCHAR ());d ROP table Orders; CREATE TABLE Orders (  modelid     Decimal (8,0) PRIMARY key,  Description VARCHAR (+),  FOREIGN KEY (modelid) REFERENCES Models (ModelID) on the    DELETE Cascade); INSERT into Models (ModelID, Name) VALUES (1, ' model '); INSERT INTO Order S (modelid,description) VALUES (1, ' order ');

When the last sentence is executed, an exception is reported

[2018-08-01 14:06:16] [23000] [1452] Cannot add or update a child row:a FOREIGN KEY constraint fails (' Bov '. ' Orders ', constraint ' orders_ibfk_1 ' Foreig N KEY (' modelid ') REFERENCES ' Models ' (' ModelID ') on DELETE CASCADE)

Cause: The ModelID of Models is decimal (6,0), and the ModelID of orders is decimal (8,0) and two are connected by foreign keys. Because the types are inconsistent, MySQL does not consider them to be equal, and Oracle can do different types of compatibility judgments.

Solution Solutions

drop table Orders; CREATE TABLE Orders (  modelid     Decimal (6,0) PRIMARY key,  Description VARCHAR (+),  FOREIGN KEY (modelid) REFERENCES Models (ModelID) on the    DELETE Cascade); INSERT into Orders (modelid,description) VALUES (1, ' order ');

SELECT * FROM Models;1    modelselect * from orders;1    order

Summarize

1. MySQL FOREIGN KEY constraint design is flawed, if different units of the field must be different, should be added foreign key on the report exception, not ambiguous because the type is different, but the actual value is equal, its judgment is not equal.

2. When a database table is maintained, the type of column with the same meaning in the table must be consistent.