Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

How to realize cascading deletion by using foreign keys in MySQL

2025-01-18 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

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

This article introduces the relevant knowledge of "how to use foreign keys to achieve cascading deletion in MySQL". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Next, let's first create the following test database table:

CREATE TABLE `roottb` (

`id`INT (11) UNSIGNED AUTO_INCREMENT NOT NULL

`data`NOT NULL DEFAULT (VARCHAR)''

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`NOT NULL DEFAULT (VARCHAR)''

PRIMARY KEY (`id`)

INDEX (`rootid`)

FOREIGN KEY (`rootid`) REFERENCES roottb (`id`) ON DELETE CASCADE

) TYPE=InnoDB

Note:

1. InnoDB engine must be used

2. Foreign keys must be indexed (INDEX)

3. Foreign key binding relationship "ON DELETE CASCADE" is used here, which means that if the corresponding data of the foreign key is deleted, the associated data will be deleted completely. For more information, please refer to the documentation on InnoDB in the MySQL manual.

Okay, then let's insert the test data:

INSERT INTO `roottb` (`id`, `data`)

VALUES ('1customers,' test root line 1')

('2percent,' test root line 2')

('3pm,' test root line 3')

INSERT INTO `subtb` (`id`, `rootid`, `data`)

VALUES ('1th,' 1th, 'test sub line 1 for root 1')

('2percent,' 1percent, 'test sub line 2 for root 1')

('3pm,' 1pm, 'test sub line 3 for root 1')

('4 for root,'2 for root 2')

('5 for root,'2 for root 2')

('6pm,' 2pm, 'test sub line 3 for root 2')

('7,'3, 'test sub line 1 for root 3')

('8pm,' 3pm, 'test sub line 2 for root 3')

('9 for root,'3 for root 3')

Let's first 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.05sec)

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's fine. Okay, now we're going to test our cascade deletion function.

We will delete only the data records with id 2 in the roottb table and see if the related sub-records with rootid 2 in the subtb table will be deleted automatically:

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, we can see that the corresponding data in the subtb table is indeed deleted automatically, and the test is successful.

Conclusion: cascading deletion is achieved successfully by using foreign keys in MySQL!

This is the end of the content of "how to use foreign keys to cascade delete in MySQL". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.

Views: 0

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report