Foreign key DDL runs normally in Oracle, but exceptions and solutions are reported in mysql

Remember a Mysql foreign key constraint design flaw

Background information

Recently doing a database migration project, from Oracle to Mysql, a foreign key constraint runs normally in Oracle, but reports in mysql abnormal. (Because I have only taken over for a few days, I am not familiar with the business and framework, so I spent a lot of time dealing 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` FOREIGN 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(40)
);

drop table Orders;
CREATE TABLE Orders
(
  ModelID     number(8) PRIMARY KEY,
  Description VARCHAR(40),
  FOREIGN KEY (ModelID) REFERENCES Models (ModelID)
  ON DELETE cascade
);

insert into Models(ModelID, Name) values (1,'model');
insert into Orders(ModelID,Description) values (1,'order');

select * from Models;
1    model

select * from Orders;
1    order

Mysql’s DDL

drop table Models;
CREATE TABLE Models
(
  ModelID decimal(6,0)  PRIMARY KEY,
  Name VARCHAR(40)
);

drop table Orders;
CREATE TABLE Orders
(
  ModelID     decimal(8,0) PRIMARY KEY,
  Description VARCHAR(40),
  FOREIGN KEY (ModelID) REFERENCES Models (ModelID)
    ON DELETE cascade
);


insert into Models(ModelID, Name) values (1,'model');
insert into Orders(ModelID,Description) values (1,'order');

When executing the last sentence, an exception was 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` FOREIGN KEY (`ModelID`) REFERENCES `Models` (`ModelID`) ON DELETE CASCADE)

Cause: The ModelID of Models is decimal(6,0), while the ModelID of Orders is decimal(8,0), and the two are connected through foreign keys. Because the types are inconsistent, MySQL will not think that they are necessarily unequal, and Oracle can determine the compatibility of different types.

Solution

drop table Orders;
CREATE TABLE Orders
(
  ModelID     decimal(6,0) PRIMARY KEY,
  Description VARCHAR(40),
  FOREIGN KEY (ModelID) REFERENCES Models (ModelID)
    ON DELETE cascade
);

insert into Orders(ModelID,Description) values (1,'order');

select * from Models;
1    model

select * from Orders;
1    order

Summary

1. The foreign key constraint design of Mysql is flawed. If the fields of different units must be different, FOREIGN KEY should be added. An exception is reported, instead of being ambiguous because the types are different, but the actual values ​​are equal, it is judged as not equal.

2. When maintaining database tables, the types of columns with the same meaning in different tables must be consistent.

Related articles:

MySQL foreign key constraint mode_MySQL

MySQL foreign key constraint cascade Delete

related videos:

Cheetah.com MySQL video tutorial

The above is the detailed content of Foreign key DDL runs normally in Oracle, but exceptions and solutions are reported in mysql. For more information, please follow other related articles on the PHP Chinese website!