mysql 级联删除_mysql级联删除操作

mysql> CREATE TABLE `roottb` (

->   `id` INT(11) UNSIGNED AUTO_INCREMENT NOT NULL,

->   `data` VARCHAR(100) NOT NULL DEFAULT '',

->   PRIMARY KEY (`id`)

-> ) engine=InnoDB;

Query OK, 0 rows affected (0.60 sec)

mysql> 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

-> ) 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)