本文共 1905 字，大约阅读时间需要 6 分钟。
今天发现按照标准SQL写法在 MySQL建表时创建的外键都没有生效 ，调查发现MySQL居然没有创建外键（使用的是最新的MySQL 5.7）。
- mysql> create table tbp(id int,pid int REFERENCES tb(id) on delete RESTRICT);
- Query OK, 0 rows affected (0.05 sec)
-
- mysql> show create table tbp;
- +-------+-----------------------------------------------------------------------------------------------------------------------+
- | Table | Create Table |
- +-------+-----------------------------------------------------------------------------------------------------------------------+
- | tbp | CREATE TABLE `tbp` (
- `id` int(11) DEFAULT NULL,
- `pid` int(11) DEFAULT NULL
- ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
- +-------+-----------------------------------------------------------------------------------------------------------------------+
- 1 row in set (0.00 sec)
换一种写法可以成功
- mysql> create table tbp2(id int,pid int, FOREIGN KEY (pid) REFERENCES tb(id) on delete RESTRICT);
- Query OK, 0 rows affected (0.04 sec)
-
- mysql> show create table tbp2;
- +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
- | Table | Create Table |
- +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
- | tbp2 | CREATE TABLE `tbp2` (
- `id` int(11) DEFAULT NULL,
- `pid` int(11) DEFAULT NULL,
- KEY `pid` (`pid`),
- CONSTRAINT `tbp2_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `tb` (`id`)
- ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
- +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
- 1 row in set (0.00 sec)
这也太坑了，不支持就应该报个错啊！
转载地址：http://rnabl.baihongyu.com/