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.