Setting up foreign keys in phpMyAdmin?

Question

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 1

I realize that sometimes it's the only option, but I'm not a huge fan of phpMyAdmin. To me it seems limited in its usefulness as an IDE... which of course I suppose one could argue it's not really meant to be.

In any case, I'd recommend installing MySQL Administrator/MySQL Query Browser. If you're on a Windows box (gasp... shame on me I suppose), I think SQL Wave is even better... though their most recent releases I'm less crazy about than the one from about 6 months ago.

Answer 2

InnoDB allows you to add a new foreign key constraint to a table by using ALTER TABLE:

ALTER TABLE tbl_name
    ADD [CONSTRAINT [symbol]] FOREIGN KEY
    [index_name] (index_col_name, ...)
    REFERENCES tbl_name (index_col_name,...)
    [ON DELETE reference_option]
    [ON UPDATE reference_option]

On the other hand, if MyISAM has advantages over InnoDB in your context, why would you want to create foreign key constraints at all. You can handle this on the model level of your application. Just make sure the columns which you want to use as foreign keys are indexed!

Answer 3

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 refering table (so foo_bar.foo_id, in your case). Then, go to relation view (in the refering table) and select the refered column (so in your case foo.id) and the on update and on delete actions.

I think foreign keys are really 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.