Setting up foreign key in phpMyAdmin

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:

Products Table

Category Table

Product Category Table

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.

InnoDB

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

Table Operation InnoDB

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.

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 foreign key constraint.

Product Category Relation

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.

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

Like this Post? Share
  • Ajeesh Babu

    thanks… 🙂

  • SKS

    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???

  • Shamroz Samule

    How to create foreign key in phpmyadmin