MYSQLÍâ¼ü(Foreign Key)µÄʹÓ㨶þ£©

×÷Õߣº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 | t