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:

foreign key in phpMyAdmin

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.

InnoDB

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

Table Operation InnoDB

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.

Product Index

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.

Product Category Relation

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

Define foreign key in phpMyAdmin

phpMyAdmin Relation View Foreign keys

After clicking the ‘Relation view’ in the ‘product_category’ table, you can set foreign keys.

foreign key in phpMyAdmin

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 articleApply 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!

You Might Interested In

3 COMMENTS

  1. SKS says:

    It shows error while i do it. First is show Relation view as in image. Then error shown in another image occur. both table are by default InnoDB and phpmyAdmin version is 4.1.14 on wamp. I noticed Relation view is quite different here than i am facing. How can i solve this???

    Reply

Leave a Reply

Enclose a code block like: <pre><code>Your Code Snippet</code></pre>.