Question

但参考表和被参照表为同一个表时，该怎样插入数据

Answer 1

可以的, 下面是一个例子.

CREATE TABLE DEPT (
id INT PRIMARY KEY,
name varchar(10),
pid INT
) ENGINE=InnoDB ;

ALTER TABLE DEPT
ADD CONSTRAINT DEPT_cons
FOREIGN KEY (pid)
REFERENCES DEPT(id);

下面是插入数据的结果.

mysql> INSERT INTO DEPT VALUES(1, '总公司', NULL);
Query OK, 1 row affected (0.04 sec)

mysql> INSERT INTO DEPT VALUES(2, '分公司', 1);
Query OK, 1 row affected (0.11 sec)

mysql> INSERT INTO DEPT VALUES(3, '办事处', 2);
Query OK, 1 row affected (0.08 sec)

mysql>
mysql> INSERT INTO DEPT VALUES(4, '非法数据', 5);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`dept`,
CONSTRAINT `DEPT_cons` FOREIGN KEY (`pid`) REFERENCES `dept` (`id`))
mysql>