Setting up foreign key in phpMyAdmin

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:

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.


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!

You Might Interested In