建立測試表
查看版本信息
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
相關文章：
相關視頻:
