Setting up foreign key in phpMyAdmin is quite easy. With help of this step by step tutorial with images, you will be able to set foreign keys within your tables.
Here in the example we have three tables. ‘products’, ‘category’ and ‘product_category’. You can see structures of these tables in the pictures below:
To create foreign keys in phpMyAdmin:
Step 1 – Convert tables type to ‘InnoDB’
Since, MySQL only supports foreign key constraints on ‘InnoDB’ tables, the first step is to make sure the tables you are using are of that type.
Convert all tables into ‘InnoDB’, if they are not already by visiting the ‘Operations’ tab shown in figure 1.1.
Step -2 – Define indexes
You must have defined primary key (or at least an indexed column) in referred table which will work as foreign key in referring table. In our case we have defined ‘product_id’ and ‘category_id’ as primary keys in ‘products’ and ‘category’ table respectively. Screenshot for ‘products’ table is given below.
View the structure of the referring table (‘product_category’) and make each referencing field (‘product_id’, ‘category_id’) an index for which you want to apply foreign key constraint.
Now come back to structure view and click ‘Relation view’.
Step 3 – Define foreign key in phpMyAdmin
After clicking ‘Relation view’ in ‘product_category’ table, you can set foreign keys.
You will be given a table where each row corresponds to an indexed column in your referring table (‘product_category’). The first dropdown in each row lets you choose which TABLE->COLUMN the indexed column references. In the second dropdown select the desired column of your referred table and click Save. That should do it.
I have written another article ‘Apply foreign key in phpMyAdmin using database query‘ that can accomplish steps two and three only in one stepped query. Read that article too to take advantage!