展开全部
给你一个参考的例子, 你参照着, 创建一个 外键约束就可以了。
首先是测试的表结构与测e5a48de588b63231313335323631343130323136353331333332623962试数据, 和你的例子一样, 是3列。
分别为: id, 父id, 数据CREATE TABLE test_self (
id INT NOT NULL,
pid INT,
value VARCHAR(10),
PRIMARY KEY(id)
);
INSERT INTO test_self VALUES(1, NULL, '.NET');
INSERT INTO test_self VALUES(2, 1, 'C#');
INSERT INTO test_self VALUES(3, 1, 'J#');
INSERT INTO test_self VALUES(4, 1, 'ASP.NET');
INSERT INTO test_self VALUES(5, 1, 'VB.NET');
INSERT INTO test_self VALUES(6, NULL, 'J2EE');
INSERT INTO test_self VALUES(7, 6, 'EJB');
INSERT INTO test_self VALUES(8, 6, 'Servlet');
INSERT INTO test_self VALUES(9, 6, 'JSP');
INSERT INTO test_self VALUES(10, NULL, 'Database');
INSERT INTO test_self VALUES(11, 10, 'DB2');
INSERT INTO test_self VALUES(12, 10, 'MySQL');
INSERT INTO test_self VALUES(13, 10, 'Oracle');
INSERT INTO test_self VALUES(14, 10, 'SQL Server');
INSERT INTO test_self VALUES(15, 13, 'PL/SQL');
INSERT INTO test_self VALUES(16, 15, 'Function');
INSERT INTO test_self VALUES(17, 15, 'Procedure');
INSERT INTO test_self VALUES(18, 15, 'Package');
INSERT INTO test_self VALUES(19, 15, 'Cursor');
INSERT INTO test_self VALUES(20, 14, 'T-SQL');
下面是 创建 外键约束, 和 测试删除 父节点, 同时删除子节点的处理。mysql> ALTER TABLE test_self
-> ADD CONSTRAINT fk_test_self
-> FOREIGN KEY (pid) REFERENCES test_self(id) ON DELETE CASCADE;
Query OK, 20 rows affected (0.05 sec)
Records: 20 Duplicates: 0 Warnings: 0
-- 测试 级联删除 层次为 1层的. (预期结果是 id=1,2,3,4,5 的被删除. )
mysql> DELETE FROM test_self WHERE ID = 1;
Query OK, 1 row affected (0.07 sec)
-- 数据核对.
mysql> select * from test_self;
+----+------+------------+
| id | pid | value |
+----+------+------------+
| 6 | NULL | J2EE |
| 7 | 6 | EJB |
| 8 | 6 | Servlet |
| 9 | 6 | JSP |
| 10 | NULL | Database |
| 11 | 10 | DB2 |
| 12 | 10 | MySQL |
| 13 | 10 | Oracle |
| 14 | 10 | SQL Server |
| 15 | 13 | PL/SQL |
| 16 | 15 | Function |
| 17 | 15 | Procedure |
| 18 | 15 | Package |
| 19 | 15 | Cursor |
| 20 | 14 | T-SQL |
+----+------+------------+
15 rows in set (0.00 sec)
-- 测试 级联删除 层次为 多层的. (预期结果是 id=10 与 10以后 的被删除. )
mysql> DELETE FROM test_self WHERE ID = 10;
Query OK, 1 row affected (0.01 sec)
mysql> select * from test_self;
+----+------+---------+
| id | pid | value |
+----+------+---------+
| 6 | NULL | J2EE |
| 7 | 6 | EJB |
| 8 | 6 | Servlet |
| 9 | 6 | JSP |
+----+------+---------+
4 rows in set (0.00 sec)