旗下导航:搜·么
当前位置:网站首页 > MySQL教程 > 正文

外键DDL在Oracle运转一般,在mysql报非常以及解决方案【MySQL教程】,foreign-key,mysql

作者:搜教程发布时间:2019-12-01分类:MySQL教程浏览:62评论:0


导读:记一个Mysql外键束缚设想缺点背景信息近来在做项目的数据库迁徙,从Oracle到Mysql,一个外键束缚在Oracle运转一般,在mysql报非常。(由于才接办没几天,对...

记一个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报非常以及解决方案的细致内容,更多请关注ki4网别的相干文章!

标签:foreign-keymysql


欢迎 发表评论: