首先,目前在产品环境可用的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中利用外键实现级联删除成功!
---------------------------------------------------------------------------------------------------------------user表:
create table user
(
userid integer not null auto_increment primary key,
username varchar(12) not null
)
type=innodb;
password表:
create table password
(
userid integer not null,
password varchar(12) not null,
index (userid),
foreign key (userid) references user (userid)
on delete cascade
on update cascade
)
type=innodb;
1、MySQL支持外键约束,并提供与其它DB相同的功能,但表类型必须为 InnoDB
2、建外键的表的那个列要加上index.