Cascade deletion using foreign keys in MySQL

First, only the InnoDB Engine allows the use of foreign keys in MySQL versions available in the product environment (4.0.x and 4.1.x). Therefore, our data tables must use the InnoDB engine.

Next, we will first create the following database tables for testing:

Create table 'roottb '(
'Id' INT (11) UNSIGNED AUTO_INCREMENT not null,
'Data' VARCHAR (100) not null default '',
Primary key ('id ')
) TYPE = InnoDB;

Create table 'subtb '(
'Id' INT (11) UNSIGNED AUTO_INCREMENT not null,
'Rootid' INT (11) unsigned not null default '0 ',
'Data' VARCHAR (100) not null default '',
Primary key ('id '),
INDEX ('rootid '),
Foreign key ('rootid') REFERENCES roottb ('id') ON DELETE CASCADE
) TYPE = InnoDB;

Note:
1. the InnoDB engine must be used;
2. The foreign key must be indexed );
3. The foreign key binding relationship uses "on delete cascade", which means that if the data corresponding to the foreign key is deleted, the associated data is completely deleted, for more information, see the InnoDB documentation in the MySQL manual;

OK. Then we can insert the test data:

Insert into 'roottb' ('id', 'data ')
VALUES ('1', 'test root line 1 '),
('2', 'test root line 2 '),
('3', 'test root line 3 ');

Insert into 'subtb' ('id', 'rootid', 'data ')
VALUES ('1', '1', 'test sub line 1 for root 1 '),
('2', '1', 'test sub line 2 for root 1 '),
('3', '1', 'test sub line 3 for root 1 '),
('4', '2', 'test sub line 1 for root 2 '),
('5', '2', 'test sub line 2 for root 2 '),
('6', '2', 'test sub line 3 for root 2 '),
('7', '3', 'test sub line 1 for root 3 '),
('8', '3', 'test sub line 2 for root 3 '),
('9', '3', 'test sub line 3 for root 3 ');

Let's take a look at the status of the current data table:

Mysql>; show tables;
+ ---------------- +
| Tables_in_test |
+ ---------------- +
| Roottb |
| Subtb |
+ ---------------- +
2 rows in set (0.00 sec)

Mysql>; select * from 'roottb ';
+ ---- + ------------------ +
| Id | data |
+ ---- + ------------------ +
| 1 | test root line 1 |
| 2 | test root line 2 |
| 3 | test root line 3 |
+ ---- + ------------------ +
3 rows in set (0.05 sec)

Mysql>; select * from 'subtb ';
+ ---- + -------- + ---------------------------- +
| Id | rootid | data |
+ ---- + -------- + ---------------------------- +
| 1 | 1 | test sub line 1 for root 1 |
| 2 | 1 | test sub line 2 for root 1 |
| 3 | 1 | test sub line 3 for root 1 |
| 4 | 2 | test sub line 1 for root 2 |
| 5 | 2 | test sub line 2 for root 2 |
| 6 | 2 | test sub line 3 for root 2 |
| 7 | 3 | test sub line 1 for root 3 |
| 8 | 3 | test sub line 2 for root 3 |
| 9 | 3 | test sub line 3 for root 3 |
+ ---- + -------- + ---------------------------- +
9 rows in set (0.01 sec)

Well, everything is normal. Well, next we will test our cascade deletion function.

We will delete only the data records with the id of 2 in the roottb table to see if the related sub-records with the id of 2 in the subtb table are automatically deleted:

Mysql>; delete from 'roottb' where 'id' = '2 ';
Query OK, 1 row affected (0.03 sec)

Mysql>; select * from 'roottb ';
+ ---- + ------------------ +
| Id | data |
+ ---- + ------------------ +
| 1 | test root line 1 |
| 3 | test root line 3 |
+ ---- + ------------------ +
2 rows in set (0.00 sec)

Mysql>; select * from 'subtb ';
+ ---- + -------- + ---------------------------- +
| Id | rootid | data |
+ ---- + -------- + ---------------------------- +
| 1 | 1 | test sub line 1 for root 1 |
| 2 | 1 | test sub line 2 for root 1 |
| 3 | 1 | test sub line 3 for root 1 |
| 7 | 3 | test sub line 1 for root 3 |
| 8 | 3 | test sub line 2 for root 3 |
| 9 | 3 | test sub line 3 for root 3 |
+ ---- + -------- + ---------------------------- +
6 rows in set (0.01 sec)

Well, check that the data in the subtb table is automatically deleted and the test is successful.

Conclusion: The cascading deletion is successful using the foreign key in MySQL!