Setting up foreign keys in phpMyAdmin?
I'm setting up a database using phpMyAdmin. I have two tables (foo and bar), indexed on their primary keys. I am trying to create a relational table (foo_bar) between them, using their primary keys as foreign keys.
I created these tables as MyISAM, but have since changed all three to InnoDB, because I read that MyISAM doesn't support foreign keys. All id fields are INT(11).
When I choose the foo_bar table, click the "relation view" link, and try to set the FK columns to be database.foo.id and database.bar.id, it says "No index defined!" beside each column.
What am I missing?
Clarification/Update
For the sake of simplicity, I want to keep using phpMyAdmin. I am currently using XAMPP, which is easy enough to let me focus on the PHP/CSS/Javascript, and it comes with phpMyAdmin.
Also, although I haven't been able to set up explicit foreign keys yet, I do have a relational table and can perform joins like this:
SELECT *
FROM foo
INNER JOIN foo_bar
ON foo.id = foo_bar.foo_id
INNER JOIN bar
ON foo_bar.bar_id = bar.id;
It just makes me uncomfortable not to have the FKs explicitly defined in the database.
Answer
If you want to use phpMyAdmin to set up relations, you have to do 2 things. First of all, you have to define an index on the foreign key column in the referring table (so foo_bar.foo_id, in your case). Then, go to relation view (in the referring table) and select the referred column (so in your case foo.id) and the on update and on delete actions.
I think foreign keys are useful if you have multiple tables linked to one another, in particular, your delete scripts will become very short if you set the referencing options correctly.
EDIT: Make sure both of the tables have the InnoDB engine selected.