记一个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
总结
Mysql的外键束缚设想有缺点,假如差别单元的字段肯定差别,应在增加FOREIGN KEY就报非常,而不是含糊其词的由于范例差别,但现实数值相称,其推断为不等于。
数据库表保护的时刻,差别table中,意义雷同的column,范例肯定要保持一致。
相干文章:
MySQL外键束缚体式格局_MySQL
MySQL的外键束缚级联删除
相干视频:
猎豹网MySQL视频教程
以上就是外键DDL在Oracle运转一般,在mysql报非常以及解决方案的细致内容,更多请关注ki4网别的相干文章!