[TOC] 建立测试表 查看版本信息 ~~~ select version(); 5.7.22 ~~~ 创建父表 ~~~ drop table if exists Models; CREATE TABLE Models ( ModelID SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, Name VARCHAR(40) NOT NULL, PRIMARY KEY (ModelID) ); ~~~ 创建子表 ~~~ drop table if exists Orders; CREATE TABLE Orders ( ID SMALLINT UNSIGNED NOT NULL PRIMARY KEY, ModelID SMALLINT UNSIGNED NOT NULL, Description VARCHAR(40), FOREIGN KEY (ModelID) REFERENCES Models (ModelID) ON DELETE cascade ); ~~~ 测试 测试用例-无父表相应数据，先插入子表 `insert into Orders(Id,ModelID,Description) values (1,1,'a');` 结果：执行失败 `异常：[2018-07-31 11:08:01] 23000 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)` 原因：通不过on delete cascade 的外键约束检查 测试用例-先插入主表数据，再插入子表数据 ~~~ insert into Models(ModelID,Name) values (1,'a'); insert into Orders(Id,ModelID,Description) values (1,1,'a'); ~~~ 结果：执行成功 ~~~ select * from Models; 1 a select * from Orders; 1 1 a ~~~ 测试用例-父子表都有数据，删除子表数据 ~~~ delete from Orders where id = 1; ~~~ 结果：执行成功 ~~~ select * from Models; 1 a select * from Orders; 为空 ~~~ 测试用例-父子表都有数据，删除父表书库 ~~~ delete from Models where ModelID = 1; ~~~ 结果：执行成功 ~~~ select * from Models; 为空 select * from Orders; 为空 ~~~ 测试用例-父子表都有数据，更新子表外键 ~~~ update Orders set ModelID = 3 where ID =1; ~~~ 结果：执行失败 `异常：[2018-07-31 12:33:02] 23000 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)` 原因：通不过on delete cascade 的外键约束检查 测试用例-父子表都有数据，更新父表主键 ~~~ update Models set ModelID = 2 where ModelID =1; ~~~ 结果：执行失败 `异常：[2018-07-31 12:34:24] 23000 Cannot delete or update a parent row: a foreign key constraint fails (bov.Orders, CONSTRAINT Orders_ibfk_1 FOREIGN KEY (ModelID) REFERENCES Models (ModelID) ON DELETE CASCADE)` 原因：通不过on delete cascade 的外键约束检查 测试用例-父子表都有数据，更新子表非外键 `update Orders set Description = 'b' where ID =1;` 结果：执行成功 ~~~ select * from Orders; 1 1 b ~~~ 测试用例-父子表都有数据，更新父表非主键 `update Models set Name = 'c' where ModelID =1;` 结果：执行成功 ~~~ select * from Models; 1 c ~~~