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

问题描述

这是我制作的表格:

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>、外键和行锁定.

The InnoDB engine should generally (unless you have a fulltext index for instance) be preferred, as InnoDB allows transactions, foreign keys, and row locking, for instance.

在测试数据库上，执行

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

(以及任何其他相关的——可能是所有的——表)

(and any other relevant - maybe all - tables)

并确保您没有任何副作用(参见 Mysql更改表).

and ensure you do not have any side effects (See Mysql Alter Table).

还要检查特定于 InnoDB 的参数(在 my.sql 中)，另请参阅 Mysql InnoDB 引擎.

Check also the parameters specific to InnoDB (in my.sql), See also the Mysql InnoDB engine.

这篇关于Mysql:添加外键不会在 MyISAM 表上给出警告/错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！