Setting up Foreign Key in phpMyAdmin

Setting up a foreign key in phpMyAdmin is quite easy. With the 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 the structures of these tables in the pictures:

Create Foreign Keys in phpMyAdmin

We’ve mentioned three steps to visually create foreign keys in phpMyAdmin. Also, you can perform the same more easily using queries as well. Check the linked article at the bottom to set foreign keys through the query in phpMyAdmin.

Convert Tables Type to ‘InnoDB’

Since MySQL only supports foreign key constraints on ‘InnoDB’ tables, the first step is to make sure the tables in the database are of InnoDB type.

Convert all tables into ‘InnoDB’, if they are not already by visiting the ‘Operations’ tab shown in figure 1.1.

Define Indexes for Foreign Keys

You must have defined a primary key (or at least an indexed column) in the referred table which will work as the foreign key in the referring table. In our case, we have defined ‘product_id’ and ‘category_id’ as primary keys in the ‘products’ and ‘category’ table respectively.  The screenshot for the ‘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 the foreign key constraint.

Now come back to the structure view and click ‘Relation view’.

Define foreign key in phpMyAdmin

After clicking the ‘Relation view’ in the ‘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!