Summary of establishing test parent tables, child tables and test cases in Mysq

Create test table

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

Test Use case - there is no corresponding data in the parent table, insert the child table first

insert into Orders(Id,ModelID,Description) values (1,1,'a');

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: The foreign key constraint check of on delete cascade cannot be passed

Test case-Insert the main table data first, and then insert the sub-table data

insert into Models(ModelID,Name) values (1,'a');
insert into Orders(Id,ModelID,Description) values (1,1,'a');

Result: Execution successful

select * from Models;
1    a

select * from Orders;
1    1    a

Test case - both parent and child tables have data, delete child table data

delete from Orders where id = 1;

Result: Execution successful

select * from Models;
1    a
select * from Orders;
为空

Test case - Parent and child tables There is data, delete the parent table library

delete from Models where ModelID = 1;

Result: Execution successful

select * from Models;
为空
select * from Orders;
为空

Test case - Both parent and child tables have data, update the foreign key of the child table

update Orders set ModelID = 3 where ID =1;

Result: Execution Failure
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: failed on delete cascade Foreign key constraint check

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)
Cause: failed the foreign key constraint check on delete cascade

Test case-both parent and child tables Data, update the non-foreign key of the child table

update Orders set Description = 'b' where ID =1;

Result: Successful execution

select * from Orders;
1    1    b

Test case - both parent and child tables have data, update the non-primary key of the parent table

update Models set Name = 'c' where ModelID =1;

Result: Execution Success

select * from Models;
1    c

Related articles:

Mysql database index establishment and performance testing

mysql high-performance stress test (summary It’s been a long time)

Related videos:

Data Structure Adventure: Linear Table

The above is the detailed content of Summary of establishing test parent tables, child tables and test cases in Mysq. For more information, please follow other related articles on the PHP Chinese website!