Shulou(Shulou.com)05/31 Report--

This article is about the example of creating test parent tables, child tables, and test cases in Mysq. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

Set up test table to view version information select version (); 5.7.22 create parent table drop table if exists Models;CREATE TABLE Models (ModelID SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, Name VARCHAR (40) NOT NULL, PRIMARY KEY (ModelID)); create child table 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) Test case-if there is no corresponding data in the parent table, insert the child table insert into Orders (Id,ModelID,Description) values first.

Result: execution failed

Exception: [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)

Reason: fail the foreign key constraint check of on delete cascade

Test case-insert the main table data first, and then insert the child table data insert into Models (ModelID,Name) values (1 Id,ModelID,Description) values (1 Id,ModelID,Description)

Result: execution succeeded

Select * from Models;1 aselect * from Orders;1 1a test case-both parent and child tables have data, delete child table data delete from Orders where id = 1

Result: execution succeeded

Select * from Models;1 aselect * from Orders; is an empty test case-both parent and child tables have data. Delete the parent table library delete from Models where ModelID = 1

Result: execution succeeded

Select * from Models; is empty select * from Orders; is empty test case-both parent and child tables have data, update child table foreign key update Orders set ModelID = 3 where ID = 1

Result: execution failed

Exception: [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)

Reason: fail the foreign key constraint check of on delete cascade

Test case-both parent and child tables have data, update the primary key of the parent table update Models set ModelID = 2 where ModelID = 1

Result: execution failed

Exception: [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)

Reason: fail the foreign key constraint check of on delete cascade

Test case-both parent and child tables have data, update child table non-foreign key update Orders set Description ='b' where ID = 1

Result: execution succeeded

Select * from Orders;1 1b test case-both parent and child tables have data, update parent table non-primary key update Models set Name ='c 'where ModelID = 1

Result: execution succeeded

Select * from Models;1 c Thank you for your reading! This is the end of this article on "examples of establishing test parent tables, child tables and test cases in Mysq". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.