Posted By: nerdposeur
I’m setting up a database using phpMyAdmin. I have two tables (
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
When I choose the
foo_bar table, click the "relation view" link, and try to set the FK columns to be
database.bar.id, it says "No index defined!" beside each column.
What am I missing?
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.
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.