做BBS论坛时,只有一张表存储大模块与小模块,好像就是树状结构表。如何删除下面的一条数据。delete from t_edition where editionid=1;(我用这个删,但报错)大虾,请教一下,。
drop database bbs_test;
create database bbs_test;
use bbs_test;create table t_edition
(
editionId int primary key auto_increment,
name varchar(20),
createDate date,
information varchar(500),
parentid int,
constraint fk_id foreign key(parentid) references t_edition(editionId)
on update no action
on delete no action
);insert into t_edition values(null,'javaSE',now(),'欢迎大家进入JAVASE技术',1);
insert into t_edition values(null,'J2EE/JSP',now(),'欢迎大家进入J2EE技术',2);
insert into t_edition values(null,'数据库',now(),'欢迎大家进入数据库技术',3);
有些复杂。或者先把 parentid 清为空,再delete
或者直接把 foreign_key_checks 给OFF掉。想不出其它方法了,看看论坛中其它朋友有没有什么好主意。
2.删除可以禁用外键
也可以将你的数据改了mysql> select * from t_edition;
+-----------+----------+------------+--------------------+----------+
| editionId | name | createDate | information | parentid |
+-----------+----------+------------+--------------------+----------+
| 2 | J2EE/JSP | 2009-09-04 | ?迎大家?入J2EE技? | 2 |
| 3 | 数据? | 2009-09-04 | ?迎大家?入数据?技? | 2 |
+-----------+----------+------------+--------------------+----------+
2 rows in set (0.00 sec)
mysql> update t_edition set parentid=null ;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> delete from t_edition where parentid is null;
3.不能删除原因是因为你的parentid和editionId关联
创建个存储过程从最低界别的开始删去 因为最低级别的没有儿子
而你先删除爷爷肯定不行了
....
mysql> select * from t_edition;
+-----------+----------+------------+------------------------+----------+
| editionId | name | createDate | information | parentid |
+-----------+----------+------------+------------------------+----------+
| 1 | javaSE | 2009-09-04 | 欢迎大家进入JAVASE技术 | 1 |
| 2 | J2EE/JSP | 2009-09-04 | 欢迎大家进入J2EE技术 | 2 |
| 3 | 数据库 | 2009-09-04 | 欢迎大家进入数据库技术 | 3 |
+-----------+----------+------------+------------------------+----------+
3 rows in set (0.00 sec)mysql> delete from t_edition where editionid=1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constrai
nt fails (`csdn`.`t_edition`, CONSTRAINT `fk_id` FOREIGN KEY (`parentid`) REFERE
NCES `t_edition` (`editionId`) ON DELETE NO ACTION ON UPDATE NO ACTION)
mysql> select * from t_edition;
+-----------+----------+------------+------------------------+----------+
| editionId | name | createDate | information | parentid |
+-----------+----------+------------+------------------------+----------+
| 1 | javaSE | 2009-09-04 | 欢迎大家进入JAVASE技术 | 1 |
| 2 | J2EE/JSP | 2009-09-04 | 欢迎大家进入J2EE技术 | 2 |
| 3 | 数据库 | 2009-09-04 | 欢迎大家进入数据库技术 | 3 |
+-----------+----------+------------+------------------------+----------+
3 rows in set (0.00 sec)mysql> update t_edition set parentid=null where editionid=1;
Query OK, 1 row affected (0.06 sec)
Rows matched: 1 Changed: 1 Warnings: 0mysql> select * from t_edition;
+-----------+----------+------------+------------------------+----------+
| editionId | name | createDate | information | parentid |
+-----------+----------+------------+------------------------+----------+
| 1 | javaSE | 2009-09-04 | 欢迎大家进入JAVASE技术 | NULL |
| 2 | J2EE/JSP | 2009-09-04 | 欢迎大家进入J2EE技术 | 2 |
| 3 | 数据库 | 2009-09-04 | 欢迎大家进入数据库技术 | 3 |
+-----------+----------+------------+------------------------+----------+
3 rows in set (0.00 sec)mysql> delete from t_edition where editionid=1;
Query OK, 1 row affected (0.06 sec)mysql> select * from t_edition;
+-----------+----------+------------+------------------------+----------+
| editionId | name | createDate | information | parentid |
+-----------+----------+------------+------------------------+----------+
| 2 | J2EE/JSP | 2009-09-04 | 欢迎大家进入J2EE技术 | 2 |
| 3 | 数据库 | 2009-09-04 | 欢迎大家进入数据库技术 | 3 |
+-----------+----------+------------+------------------------+----------+
2 rows in set (0.00 sec)mysql>
mysql> delete from t_edition where editionid=2;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constrai
nt fails (`csdn`.`t_edition`, CONSTRAINT `fk_id` FOREIGN KEY (`parentid`) REFERE
NCES `t_edition` (`editionId`) ON DELETE NO ACTION ON UPDATE NO ACTION)
mysql> set foreign_key_checks=0;
Query OK, 0 rows affected (0.00 sec)mysql> delete from t_edition where editionid=2;
Query OK, 1 row affected (0.06 sec)mysql> select * from t_edition;
+-----------+--------+------------+------------------------+----------+
| editionId | name | createDate | information | parentid |
+-----------+--------+------------+------------------------+----------+
| 3 | 数据库 | 2009-09-04 | 欢迎大家进入数据库技术 | 3 |
+-----------+--------+------------+------------------------+----------+
1 row in set (0.00 sec)mysql>
“把 foreign_key_checks 给OFF掉”,我用3楼那种做法测试貌似没有问题