Foreign keys must be Index in mySQL?

问题:

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

1. user_id int primary auto_increment
2. username varchar(20)
3. password varchar(20)

posts

1. post_id in primary auto_increment
2. title varchar(100)
3. message text
4. 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?

回答1:

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

回答2:

From the MySQL Reference Manual:

InnoDB requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Such an index is created on the referencing table automatically if it does not exist. (This is in contrast to some older versions, in which indexes had to be created explicitly or the creation of foreign key constraints would fail.) index_name, if given, is used as described previously.

In contrast to SQL Server, which does not require FKs to be indexed... however the recommendations that I've seen suggest that you almost always being indexing your FKs even though it isn't required.

回答3:

From the documentation:

InnoDB requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order.