sql代码如下: drop table if exists type; create table type( id int(4) AUTO_INCREMENT not null, rootid int(4) not null default 0, name varchar(20) not null default '', primary key(id), index(rootid), CONSTRAINT fk_rootid foreign key(rootid) references type(id) on delete cascade ); insert into type(name) values('运动项目');
mysql> create table type1( -> id int(4) not null, -> rootid int(4) not null default 0, -> name varchar(20) not null default '', -> primary key(id), -> index(rootid), -> CONSTRAINT fk_rootid foreign key(rootid) references type1(id) on delete cascade -> ); Query OK, 0 rows affected (0.03 sec)mysql> set @a=0; Query OK, 0 rows affected (0.00 sec)mysql> set @a=@a+1;insert into type1 values(@a, @a, 'abc'); Query OK, 0 rows affected (0.04 sec)Query OK, 1 row affected (0.01 sec)mysql> set @a=@a+1;insert into type1 values(@a, 1, 'abcd'); Query OK, 0 rows affected (0.00 sec)Query OK, 1 row affected (0.01 sec)mysql> set @a=@a+1;insert into type1 values(@a, 2, 'abcde'); Query OK, 0 rows affected (0.00 sec)Query OK, 1 row affected (0.01 sec)mysql> select * from type1; +----+--------+-------+ | id | rootid | name | +----+--------+-------+ | 1 | 1 | abc | | 2 | 1 | abcd | | 3 | 2 | abcde | +----+--------+-------+ 3 rows in set (0.00 sec)
MySQL 中无法解决这个应用问题。MySQL的外键不允许为NULL。 所以在插入第一个节点的时候比较麻烦。一般这种应用下是不在使用数据库的外键约束。
建议你列出你的表结构,并提供测试数据以及基于这些测试数据的所对应正确结果。
参考一下这个贴子的提问方式http://topic.csdn.net/u/20091130/20/8343ee6a-417c-4c2d-9415-fa46604a00cf.html
1. 你的 create table xxx .. 语句
2. 你的 insert into xxx ... 语句
3. 结果是什么样,(并给以简单的算法描述)
4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL)
这样想帮你的人可以直接搭建和你相同的环境,并在给出方案前进行测试,避免文字描述理解上的误差。
drop table if exists type;
create table type(
id int(4) AUTO_INCREMENT not null,
rootid int(4) not null default 0,
name varchar(20) not null default '',
primary key(id),
index(rootid),
CONSTRAINT fk_rootid foreign key(rootid) references type(id) on delete cascade
);
insert into type(name) values('运动项目');
-> id int(4) not null,
-> rootid int(4) not null default 0,
-> name varchar(20) not null default '',
-> primary key(id),
-> index(rootid),
-> CONSTRAINT fk_rootid foreign key(rootid) references type1(id) on delete cascade
-> );
Query OK, 0 rows affected (0.03 sec)mysql> set @a=0;
Query OK, 0 rows affected (0.00 sec)mysql> set @a=@a+1;insert into type1 values(@a, @a, 'abc');
Query OK, 0 rows affected (0.04 sec)Query OK, 1 row affected (0.01 sec)mysql> set @a=@a+1;insert into type1 values(@a, 1, 'abcd');
Query OK, 0 rows affected (0.00 sec)Query OK, 1 row affected (0.01 sec)mysql> set @a=@a+1;insert into type1 values(@a, 2, 'abcde');
Query OK, 0 rows affected (0.00 sec)Query OK, 1 row affected (0.01 sec)mysql> select * from type1;
+----+--------+-------+
| id | rootid | name |
+----+--------+-------+
| 1 | 1 | abc |
| 2 | 1 | abcd |
| 3 | 2 | abcde |
+----+--------+-------+
3 rows in set (0.00 sec)
所以在插入第一个节点的时候比较麻烦。一般这种应用下是不在使用数据库的外键约束。
mysql> set @a=0;
Query OK, 0 rows affected (0.00 sec)mysql> set @a=@a+1;insert into type1 values(@a, @a, 'abc');
Query OK, 0 rows affected (0.04 sec)Query OK, 1 row affected (0.01 sec)mysql> set @a=@a+1;insert into type1 values(@a, 1, 'abcd');
Query OK, 0 rows affected (0.00 sec)Query OK, 1 row affected (0.01 sec)mysql> set @a=@a+1;insert into type1 values(@a, 2, 'abcde');
Query OK, 0 rows affected (0.00 sec)Query OK, 1 row affected (0.01 sec)这是什么意思啊,看不懂,菜鸟求解
这篇文章也许可以帮到你