MySQL在创建外键时会给出成功消息，但实际上它并没有创建外键

Question

我想在data和containers之间添加第二个外键约束。当我删除一个容器时，链接到这个容器的数据也必须被删除。

表：

mysql> DESCRIBE data;
+------------------------+--------------+------+-----+---------------------+----------------+
| Field                  | Type         | Null | Key | Default             | Extra          |
+------------------------+--------------+------+-----+---------------------+----------------
| imei                   | varchar(15)  | NO   | MUL | NULL                |                

mysql> DESCRIBE containers;
+--------------------+-------------+------+-----+---------------------+----------------+
| Field              | Type        | Null | Key | Default             | Extra          |
+--------------------+-------------+------+-----+---------------------+----------------+

| imei               | varchar(15) | NO   | MUL | NULL                |                |

我用下面的语句创建一个外键：

 mysql> ALTER TABLE `data` ADD FOREIGN KEY (`imei`) REFERENCES `containers`(`imei`) ON DELETE CASCADE;
    Query OK, 15168 rows affected (0.12 sec)
    Records: 15168  Duplicates: 0  Warnings: 0

但尚未创建外键：

 mysql> use INFORMATION_SCHEMA;
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A

    Database changed
    mysql> select TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME from KEY_COLUMN_USAGE where REFERENCED_TABLE_NAME = 'containers';
    +----------------+-------------+-----------------------+-----------------------+------------------------+
    | TABLE_NAME     | COLUMN_NAME | CONSTRAINT_NAME       | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME |
    +----------------+-------------+-----------------------+-----------------------+------------------------+
    | container_logs | imei        | container_logs_ibfk_1 | containers            | imei                   |
    +----------------+-------------+-----------------------+-----------------------+------------------------+
    1 row in set (0.16 sec)

我做错了什么？

Answer 1

并不是所有的存储引擎都支持外键；不支持某些SQL特性的存储引擎不会产生错误，而只是忽略语句(实际上SQL解析器在MySQL架构中是较高层的，它使用较低级别的API与存储引擎通信)。

因此，如果使用的是MyISAM，则无法创建外键，但不会返回任何错误