MySQL Cascade delete operation

mysql> CREATE TABLE ' ROOTTB ' (

' ID ' INT (one) UNSIGNED auto_increment not NULL,

' Data ' VARCHAR (+) not NULL DEFAULT ' ',

-PRIMARY KEY (' id ')

) Engine=innodb;

Query OK, 0 rows affected (0.60 sec)

mysql> CREATE TABLE ' SUBTB ' (

' ID ' INT (one) UNSIGNED auto_increment not NULL,

' Rootid ' INT (one) UNSIGNED not NULL DEFAULT ' 0 ',

' Data ' VARCHAR (+) not NULL DEFAULT ' ',

-PRIMARY KEY (' id '),

INDEX (' Rootid '),

-FOREIGN KEY (' Rootid ') REFERENCES ROOTTB (' id ') on DELETE CASCADE

) Engine=innodb;

Query OK, 0 rows affected (0.16 sec)

mysql> INSERT into ' ROOTTB ' (' id ', ' data ')

VALUES (' 1 ', ' Test root Line 1 '),

(' 2 ', ' Test root Line 2 '),

(' 3 ', ' Test root Line 3 ');

Query OK, 3 rows affected (0.08 sec)

Records:3 duplicates:0 warnings:0

Mysql>

mysql> 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 ');

Query OK, 9 rows affected (0.14 sec)

Records:9 duplicates:0 warnings:0

mysql> Delete from ' ROOTTB ' where ' id ' = ' 2 ';

Query OK, 1 row affected (0.16 sec)

Mysql> select * from ' ROOTTB ';

+----+------------------+

| ID | Data |

+----+------------------+

| 1 | Test Root Line 1 |

| 3 | Test Root Line 3 |

+----+------------------+

2 rows in Set (0.02 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.00 sec)

This article is from the Linux OPS blog, so be sure to keep this source http://2853725.blog.51cto.com/2843725/1561019

MySQL Cascade delete operation