外键必须是在MySQL中的索引? [英] Foreign keys must be Index in mySQL?
问题描述
这是我的表:
用户
$ b
- user_id int primary auto_increment
- username varchar(20)
- 密码varchar(20)
帖子
$ b- 在主auto_increment中添加post_id
- title varchar(100)
- 消息文本
- user_id int
当我尝试向引用的用户添加外键时,在mySQL面板的选项列表Relation_view窗口中,我看不到
posts-> user_id
选项。
我想知道是否应该将 posts-> user_id
定义为 Index
什么的?如果是这样,为什么?
简短的回答:是的,MySQL迫使你索引外键。 b
InnoDB需要对外键和引用键进行索引,以便外键检查可以很快并且不需要表扫描。
您可以阅读更多关于MySQL文档页面上的外键: http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html
I've just created my first mySQL table on my own (other than using Joomla, Wordpress, etc.) and I am MS SQL developer for years but normally I can easily create a foreign key in MS SQL but I came across a difficulty or lack of knowledge here.
Here is my tables :
users
- user_id int primary auto_increment
- username varchar(20)
- password varchar(20)
posts
- post_id in primary auto_increment
- title varchar(100)
- message text
- user_id int
When I try to add a foreign key to users which refers to posts->user_id
, I cannot see the posts->user_id
option in the option list Relation_view window on mySQL panel.
I am wondering whether I should define posts->user_id
as Index
or something? If so, why?
Short answer: Yes, MySQL forces you to index foreign key.
InnoDB requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan.
You can read more about foreign keys on MySQL documentation pages: http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html
这篇关于外键必须是在MySQL中的索引?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!