MySQL中利用外键实现级联删除、更新

以下为原创，转载请注明出处！

作者：Dirk(dirk.yeATgmail.com)
Url：http://dirk.pdx.cn

首先，目前在产品环境可用的MySQL版本（指4.0.x和4.1.x）中，只有InnoDB引擎才允许使用外键，所以，我们的数据表必须使用InnoDB引擎。
下面，我们先创建以下测试用数据库表：
CREATETABLE`roottb`(
`id`INT(11)UNSIGNEDAUTO_INCREMENTNOTNULL,
`data`VARCHAR(100)NOTNULLDEFAULT'',
PRIMARYKEY(`id`)
)TYPE=InnoDB;
CREATETABLE`subtb`(
`id`INT(11)UNSIGNEDAUTO_INCREMENTNOTNULL,
`rootid`INT(11)UNSIGNEDNOTNULLDEFAULT'0',
`data`VARCHAR(100)NOTNULLDEFAULT'',
PRIMARYKEY(`id`),
INDEX(`rootid`),
FOREIGNKEY(`rootid`)REFERENCESroottb(`id`)ONDELETECASCADE
)TYPE=InnoDB;


注意：
1、必须使用InnoDB引擎；
2、外键必须建立索引（INDEX）；
3、外键绑定关系这里使用了“ONDELETECASCADE”，意思是如果外键对应数据被删除，将关联数据完全删除，更多信息请参考MySQL手册中关于InnoDB的文档；
好，接着我们再来插入测试数据：
INSERTINTO`roottb`(`id`,`data`)
VALUES('1','testrootline1'),
('2','testrootline2'),
('3','testrootline3');
INSERTINTO`subtb`(`id`,`rootid`,`data`)
VALUES('1','1','testsubline1forroot1'),
('2','1','testsubline2forroot1'),
('3','1','testsubline3forroot1'),
('4','2','testsubline1forroot2'),
('5','2','testsubline2forroot2'),
('6','2','testsubline3forroot2'),
('7','3','testsubline1forroot3'),
('8','3','testsubline2forroot3'),
('9','3','testsubline3forroot3');

我们先看一下当前数据表的状态：
mysql>;showtables;
+----------------+
|Tables_in_test|
+----------------+
|roottb|
|subtb|
+----------------+
2rowsinset(0.00sec)
mysql>;select*from`roottb`;
+----+------------------+
|id|data|
+----+------------------+
|1|testrootline1|
|2|testrootline2|
|3|testrootline3|
+----+------------------+
3rowsinset(0.05sec)
mysql>;select*from`subtb`;
+----+--------+----------------------------+
|id|rootid|data|
+----+--------+----------------------------+
|1|1|testsubline1forroot1|
|2|1|testsubline2forroot1|
|3|1|testsubline3forroot1|
|4|2|testsubline1forroot2|
|5|2|testsubline2forroot2|
|6|2|testsubline3forroot2|
|7|3|testsubline1forroot3|
|8|3|testsubline2forroot3|
|9|3|testsubline3forroot3|
+----+--------+----------------------------+
9rowsinset(0.01sec)


嗯，一切都正常，好，下面我们要试验我们的级联删除功能了。
我们将只删除roottb表中id为2的数据记录，看看subtb表中rootid为2的相关子纪录是否会自动删除：
mysql>;deletefrom`roottb`where`id`='2';
QueryOK,1rowaffected(0.03sec)
mysql>;select*from`roottb`;
+----+------------------+
|id|data|
+----+------------------+
|1|testrootline1|
|3|testrootline3|
+----+------------------+
2rowsinset(0.00sec)
mysql>;select*from`subtb`;
+----+--------+----------------------------+
|id|rootid|data|
+----+--------+----------------------------+
|1|1|testsubline1forroot1|
|2|1|testsubline2forroot1|
|3|1|testsubline3forroot1|
|7|3|testsubline1forroot3|
|8|3|testsubline2forroot3|
|9|3|testsubline3forroot3|
+----+--------+----------------------------+
6rowsinset(0.01sec)


嗯，看subtb表中对应数据确实自动删除了，测试成功。

结论：在MySQL中利用外键实现级联删除成功！

原文链接：http://bbs.chinaunix.net/viewthread.php?tid=462977