Mysql:添加外键不会在 MyISAM 表上给出警告/错误

Mysql: adding foreign key does not give warning/error on MyISAM tables(Mysql:添加外键不会在 MyISAM 表上给出警告/错误)

问题描述

这是我制作的表格:

mysql> show create table notes;
+-------+----------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                  |
+-------+----------------------------------------------------+
| notes | CREATE TABLE `notes` (
  `id` int(11) NOT NULL auto_increment,
  `note` text NOT NULL,
  `status` enum('active','hidden','deleted','followup','starred') default NULL,
  `created` datetime NOT NULL,
  `last_updated` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+----------------------------------------+

我尝试添加外键约束:

mysql> alter table notes add constraint foreign key(`id`) references `notetypes`.`id` on update cascade on delete restrict;
Query OK, 0 rows affected (0.15 sec)
Records: 0  Duplicates: 0  Warnings: 0

没有错误！没有警告！由于这个原因，一段时间以来，我一直在使用没有外键(假设它们存在)的内部数据库.知道这是一个错误还是我做错了什么?mysql中的任何解决方法或选项可以避免此类陷阱吗?

No errors! No warnings! Because of this reason, I have been using a internal database without foreign keys (assuming they were present) for some time now. Any idea if this is a bug or am I doing something wrong? Any workarounds or options in mysql that would avoid such pitfalls?

$ mysql --version
mysql  Ver 14.12 Distrib 5.0.75, for debian-linux-gnu (i486) using readline 5.2

<小时>

谢谢

日本

推荐答案

InnoDB 引擎通常应该(除非你有一个 fulltext 索引)是首选，因为 InnoDB 允许 transactions<例如，/em>、外键和行锁定.

  ALTER TABLE notes ENGINE = InnoDB;
  ALTER TABLE notetypes ENGINE = InnoDB;