Shulou(Shulou.com)06/01 Report--

This article will explain in detail about the foreign key DDL running normally in Oracle and how to solve the exception in mysql newspaper. The editor thinks it is very practical, so I share it with you for reference. I hope you can get something after reading this article.

Write down the background information of a Mysql foreign key constraint design defect

Recently, the database migration of the project is being done, from Oracle to Mysql, a foreign key constraint works normally in Oracle, and an exception is reported in mysql. (because he has only been in charge for a few days and is not familiar with the business and framework, he spends a lot of time dealing with problems. )

[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) DDLdrop table Models;CREATE TABLE Models (ModelID number (6) PRIMARY KEY, Name VARCHAR (40)) of Oracle; 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, insert into Orders (ModelID,Description) values, DDLdrop table Models;CREATE TABLE Models of select * from Models;1 modelselect * from Orders;1 orderMysql (ModelID decimal (6) PRIMARY KEY, Name VARCHAR (40)); drop table Orders;CREATE TABLE Orders (ModelID decimal (8) PRIMARY KEY, Description VARCHAR (40), FOREIGN KEY (ModelID) REFERENCES Models (ModelID) ON DELETE cascade) Insert into Models (ModelID, Name) values (1Magneto model'); insert into Orders (ModelID,Description) values (1memeorder')

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

Reason: Models's ModelID is decimal (6jue 0), while Orders's ModelID is decimal (8jue 0), and the two are connected by foreign keys. Because the type is inconsistent, mysql will not think that it must be different, but oracle can do different types of compatibility judgment and so on.

Solution drop table Orders;CREATE TABLE Orders (ModelID decimal (6) PRIMARY KEY, Description VARCHAR (40), FOREIGN KEY (ModelID) REFERENCES Models (ModelID) ON DELETE cascade); insert into Orders (ModelID,Description) values (1); select * from Models;1 modelselect * from Orders;1 order summary

The foreign key constraint design of Mysql is defective. If the fields of different units must be different, the exception should be reported when adding FOREIGN KEY, rather than ambiguous because the type is different, but the actual value is equal, it is judged to be not equal.

When maintaining database tables, the types of column with the same meaning in different table must be the same.

About the foreign key DDL running normally in Oracle, how to solve the exception in mysql report is shared here. I hope the above content can be helpful to you and learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.