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

问题描述

这是我制作的表格：

  mysql>显示创建表笔记; 
 + ------- + ------------------------------------- --------------- + 
 |表|创建表| 
 + ------- + ------------------------------------- --------------- + 
 |笔记| CREATE TABLE`notes`（
`id` int（11）NOT NULL auto_increment，$ b $`note` text NOT NULL，$ b $``status` enum（'active'，'hidden'，'删除'，'后续'，'星号'）默认NULL，
`created`日期时间NOT NULL，
`last_updated`时间戳NOT NULL默认CURRENT_TIMESTAMP更新CURRENT_TIMESTAMP，
 PRIMARY KEY `）
）ENGINE = MyISAM DEFAULT CHARSET = latin1 | 
 + ------- + ------------------------------------- --- + 
  

我尝试添加一个外键约束：

  mysql> alter table notes添加约束外键（`id`）引用`notetypes`.`id` on update cascade on delete restrict; 
 Query OK，0 rows affected（0.15 sec）
 Records：0 Duplicates：0 Warnings：0 
  

没有错误！没有警告！由于这个原因，我一直在使用没有外键的内部数据库（假设他们在场）。任何想法，如果这是一个错误，或者我做错了什么？在mysql中的任何变通或选项，可以避免这样的陷阱？


$ b $ pre $ $ c $ $ $ mysql $ - -version
mysql Ver 14.12 Distrib 5.0.75，用于debian-linux-gnu（i486）使用readline 5.2

---

感谢

JP

解决方案 InnoDB引擎通常应该（除非你有一个全文索引），因为InnoDB允许事务，外键和行锁定，例如。

在测试数据库上，执行

  ALTER TABLE注释ENGINE = InnoDB; 
 ALTER TABLE notetypes ENGINE = InnoDB; 
  

（和任何其他相关的 - 也许是所有的表）

，并确保您没有任何副作用（请参阅 Mysql Alter Table ）。
$ b

检查InnoDB的特定参数（在 my.sql ），另见 Mysql InnoDB引擎。

Here is a table I made:

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 |
+-------+----------------------------------------+

I try to add a foreign key constraint:

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

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

---

thanks

JP

解决方案

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.

On a test database, perform

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

(and any other relevant - maybe all - tables)

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

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

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