以下為原創，轉載請注明出處！?
作者：Dirk?(dirk.ye?AT?gmail.com)?
Url：http://dirk.pdx.cn
日期：2004/12/08?
首先，目前在產品環境可用的MySQL版本（指4.0.x和4.1.x）中，只有InnoDB引擎才允許使用外鍵，所以，我們的數據表必須使用InnoDB引擎。
下面，我們先創建以下測試用數據庫表：
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;
注意：
1、必須使用InnoDB引擎；
2、外鍵必須建立索引（INDEX）；
3、外鍵綁定關系這里使用了“?ON?DELETE?CASCADE”，意思是如果外鍵對應數據被刪除，將關聯數據完全刪除，更多信息請參考MySQL手冊中關于InnoDB的文檔；
好，接著我們再來插入測試數據：
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');
??
我們先看一下當前數據表的狀態：
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)
嗯，一切都正常，好，下面我們要試驗我們的級聯刪除功能了。
我們將只刪除roottb表中id為2的數據記錄，看看subtb表中rootid為2的相關子紀錄是否會自動刪除：
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)
嗯，看subtb表中對應數據確實自動刪除了，測試成功。
結論：在MySQL中利用外鍵實現級聯刪除成功！