外键DDL在Oracle运行正常，在mysql报异常以及解决方案-木庄网络博客

本文摘自PHP中文网，作者php是最好的语言，侵删。

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

背景信息

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

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

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

1	`[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

总结

Mysql的外键约束设计有缺陷，如果不同单位的字段一定不同，应在添加FOREIGN KEY就报异常，而不是模棱两可的因为类型不同,但实际数值相等，其判断为不等于。
数据库表维护的时候，不同table中，意义相同的column，类型一定要保持一致。

MySQL外键约束方式_MySQL

MySQL的外键约束级联删除

数据库系统概念第6版

￥41.1元 机械工业出版社

本书主要讲述了数据模型、基于对象的数据库和XML、数据存储和查询、事务管理、体系结构等方面的内容。

转载请注明出处：木庄网络博客 » 外键DDL在Oracle运行正常，在mysql报异常以及解决方案

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