mysql> create database test;
Query OK, 1 row affected (0.01 sec)
mysql> use test;
Database changed
mysql> create table one (id int not null primary key);
Query OK, 0 rows affected (0.03 sec)
mysql> -- here is the problem
mysql> create table two (oneid int not null references one(id));
Query OK, 0 rows affected (0.02 sec)
mysql> -- here are the first signs of issues!!!!
mysql> show create table two;
+-------+----------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------+
| two | CREATE TABLE `two` (
`oneid` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> -- here is the issue: an insert with no reference
mysql> insert into two values (-12);
Query OK, 1 row affected (0.01 sec)
mysql> select * from two;
+-------+
| oneid |
+-------+
| -12 |
+-------+
1 row in set (0.00 sec)
mysql> -- if you want to know:
mysql> SHOW Variables WHERE Variable_name='foreign_key_checks';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| foreign_key_checks | ON |
+--------------------+-------+
1 row in set (0.00 sec)
经过我的研究,这里唯一的问题是:为什么MySql没有因为表2的无效语法而拒绝创建,并在没有外键引用的情况下悄悄地创建了该表?
只为完整性起见,这里是MySql的正确语法。
mysql> create table three(oneid int not null, CONSTRAINT whatEverName FOREIGN KEY (oneid) REFERENCES one(id));
Query OK, 0 rows affected (0.04 sec)