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 中是否有可以避免此类陷阱的解决方法或选项？
$ mysql --version
mysql Ver 14.12 Distrib 5.0.75, for debian-linux-gnu (i486) using readline 5.2
谢谢
太平绅士
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
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
myISAM 不执行引用完整性。添加此类约束似乎是合法的，因此不会出现错误，但只要引擎是 myISAM，就不会强制执行约束。不过，它确实存储了创建的密钥，以便用户可以看到其意图。
myISAM doesn't do referential integrity. it seems it is legal to add such constraints and hence no error, though the constraints won't be enforced as long as the engine is myISAM. It does store the keys created so the user can see what was intended, though.
InnoDB 引擎通常应该是首选（除非您有全文索引），因为 InnoDB 允许事务、外键和例如，>行锁定。
在测试数据库上，执行
（以及任何其他相关 - 也许所有 - 表）
并确保没有任何副作用（请参阅 Mysql 更改表）。
另请检查特定于 InnoDB 的参数（在
my.sql中），另请参阅 Mysql InnoDB 引擎。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
(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.