一个表的字段的外键能否指向同表的主键?

Question

部门ID(主键)
上级部门ID(能否参考同表的"部门ID"作为外键)?顶级部门的"级部门ID"可为空.

Answer 1

可以mysql> create table test4(a int primary key,b int,foreign key (b) references test4(a));
Query OK, 0 rows affected (0.12 sec)mysql> show create table test4;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                   |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test4 | CREATE TABLE `test4` (
  `a` int(11) NOT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`a`),
  KEY `b` (`b`),
  CONSTRAINT `test4_ibfk_1` FOREIGN KEY (`b`) REFERENCES `test4` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)mysql> insert into test4 values(1,1);
Query OK, 1 row affected (0.02 sec)mysql> insert into test4 values(1,2);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'