MYSQ l establish test parent table, child table and test case summary

Build a Test table

View version Information

Select version (); 5.7.22

Create a parent table

drop table if exists Models; CREATE TABLE Models  (    modelid SMALLINT UNSIGNED not null auto_increment,   Name VARCHAR (+) 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 (+),    FOREIGN KEY (modelid) REFERENCES Models (modelid) on      DELETE Cascade  );

Test

Test Case-no parent table corresponding data, insert child table first

Insert into Orders (id,modelid,description) VALUES (n, ' 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 c2/> FOREIGN KEY ( ModelID ) REFERENCES Models ( ModelID ) on DELETE CASCADE)
Reason: FOREIGN KEY constraint check on DELETE cascade

Test Cases-Insert the main table data and insert the child table data first

Insert into Models (modelid,name) VALUES (1, ' a ') and insert into Orders (id,modelid,description) VALUES (n, ' a ');

Result: Successful execution

SELECT * FROM Models;1    aselect * from orders;1    1    A

Test cases-parent-child tables have data, delete child table data

Delete from Orders where id = 1;

Result: Successful execution

SELECT * FROM Models;1    aselect * from Orders;

Test cases-parent-child tables have data, delete the Parents table library

Delete from Models where modelid = 1;

Result: Successful execution

SELECT * from Models; empty select * from Orders;

Test cases-parent-child tables have data, update child table foreign keys

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 c2/> FOREIGN KEY ( ModelID ) REFERENCES Models ( ModelID ) on DELETE CASCADE)
Reason: FOREIGN KEY constraint check on DELETE cascade

Test cases-parent-child tables have data, update the primary key

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 , Constrai NT Orders_ibfk_1 FOREIGN KEY ( ModelID ) REFERENCES Models ( ModelID ) on DELETE CASCADE)
Reason: FOREIGN KEY constraint check on DELETE cascade

Test cases-parent-child tables have data, update child table non-foreign keys

Update Orders Set Description = ' B ' where ID = 1;

Result: Successful execution

SELECT * from orders;1    1    b

Test cases-parent-child tables have data, update the non-primary key

Update Models Set Name = ' C ' where modelid = 1;

Result: Successful execution

SELECT * FROM Models;1    c

Related articles:

MySQL database index creation and performance testing

MySQL High performance stress test (summed up for a long time)

Related videos:

Linear table of data structure exploration