当前位置:Gxlcms > mysql > 外键DDL在Oracle运行正常,在mysql报异常以及解决方案

外键DDL在Oracle运行正常,在mysql报异常以及解决方案

时间:2021-07-01 10:21:17 帮助过:53人阅读

记一个Mysql外键约束设计缺陷

背景信息

最近在做项目的数据库迁移,从Oracle到Mysql,一个外键约束在Oracle运行正常,在mysql报异常。(因为才接手没几天,对业务和框架不熟,在处理问题时花了很多时间。)

[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的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的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');

在执行最后一句时,报异常

[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)

原因:Models的ModelID是decimal(6,0),而Orders的ModelID是decimal(8,0),两个通过外键相连。因为类型不一致,mysql就不会认为其一定不等,而oracle可以做到不同类型的相容判等。

解决方案
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

总结

  1. Mysql的外键约束设计有缺陷,如果不同单位的字段一定不同,应在添加FOREIGN KEY就报异常,而不是模棱两可的因为类型不同,但实际数值相等,其判断为不等于。

  2. 数据库表维护的时候,不同table中,意义相同的column,类型一定要保持一致。

相关文章:

MySQL外键约束方式_MySQL

MySQL的外键约束级联删除

相关视频:

猎豹网MySQL视频教程

以上就是外键DDL在Oracle运行正常,在mysql报异常以及解决方案的详细内容,更多请关注Gxl网其它相关文章!

人气教程排行