求教：Mysql能否建立外键参考自身的字段

Question

一个无限分类的数据库，字段如下：
1、id
2、pid  (父亲id)
3、name现在想：如果删除一个分类，想让它下面的所有子分类一起删除，于是想让pid参考自身的id以下是SQL语句：
ALTER TABLE `gaojiao_class`
  ADD FOREIGN KEY (`pid`) REFERENCES `gaojiao_class` (`id`) 
  ON DELETE CASCADE;结果mysql报出以下错误：Cannot add or update a child row: a foreign key constraint fails请教：MYSQL不能这样做吗，有没有其他替代的做法，求各位大侠赐教

Answer 1

楼主是在开玩笑。mysql> create table gaojiao_class(id int primary key,pid int , name varchar(10))
 engine='innodb';
Query OK, 0 rows affected (0.16 sec)mysql> alter table gaojiao_class add FOREIGN KEY (`pid`) REFERENCES gaojiao_clas
s (`id`) ;
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql>

Answer 2

delete from a where a.pid=100;
delete from a where a.id=100;
两条命令来处理就可以实现了。不过要注意放到一个事务里，保证数据的一致性。还可以用触发器来做，那就麻烦多了。

Answer 3

mysql> create table gaojiao_class(id int primary key,pid int , name varchar(10))
 engine='innodb';
Query OK, 0 rows affected (0.01 sec)mysql> alter table gaojiao_class add FOREIGN KEY (`pid`) REFERENCES gaojiao_clas
s (`id`) ON DELETE CASCADE;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> insert into gaojiao_class values (1,null,'a01');
Query OK, 1 row affected (0.01 sec)mysql> insert into gaojiao_class values (2,4,'a01');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint f
ails (`csdn`.`gaojiao_class`, CONSTRAINT `gaojiao_class_ibfk_1` FOREIGN KEY (`pi
d`) REFERENCES `gaojiao_class` (`id`) ON DELETE CASCADE)
mysql> insert into gaojiao_class values (2,1,'a01');
Query OK, 1 row affected (0.01 sec)mysql> insert into gaojiao_class values (3,2,'a01');
Query OK, 1 row affected (0.01 sec)mysql> insert into gaojiao_class values (4,2,'a01');
Query OK, 1 row affected (0.01 sec)mysql> insert into gaojiao_class values (5,2,'a01');
Query OK, 1 row affected (0.00 sec)mysql> insert into gaojiao_class values (6,3,'a01');
Query OK, 1 row affected (0.02 sec)mysql> select * from gaojiao_class;
+----+------+------+
| id | pid  | name |
+----+------+------+
|  1 | NULL | a01  |
|  2 |    1 | a01  |
|  3 |    2 | a01  |
|  4 |    2 | a01  |
|  5 |    2 | a01  |
|  6 |    3 | a01  |
+----+------+------+
6 rows in set (0.00 sec)mysql> show create table gaojiao_class;
+---------------+---------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
---------+
| Table         | Create Table         |
+---------------+---------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
---------+
| gaojiao_class | CREATE TABLE `gaojiao_class` (
  `id` int(11) NOT NULL,
  `pid` int(11) DEFAULT NULL,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `pid` (`pid`),
  CONSTRAINT `gaojiao_class_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `gaojiao_clas
s` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+---------------+---------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
---------+
1 row in set (0.00 sec)mysql> delete from gaojiao_class where id=2;
Query OK, 1 row affected (0.02 sec)mysql> select * from gaojiao_class;
+----+------+------+
| id | pid  | name |
+----+------+------+
|  1 | NULL | a01  |
+----+------+------+
1 row in set (0.00 sec)mysql>