Apply foreign key in phpMyAdmin using database query

If you already have database tables and you just want to apply foreign keys for record on those table then you can apply foreign key in phpMyAdmin using database query also.

I have already written a tutorial ‘Setting up foreign key in phpMyAdmin‘ with example images and I prefer to write database query to set up foreign key in existing database tables as queries allow me to accomplish all required steps in once go.

Note: This article is a sequel of ‘Setting up foreign key in phpMyAdmin‘ and for a complete understanding, please visit this previous one. Here in this article I’m mentioning queries to define indexes on fields as well as foreign key. Please follow the previous article and ensure the tables type is ‘InnoDB‘.

If we have two tables called products (primary key: ‘product_id‘) and category (primary key: ‘category_id‘) then for a table named product_category shown in the example below, we will write and execute queries to apply foreign key constraints on fields ‘product_id‘ and ‘category_id‘.

To apply foreign key in phpMyAdmin using database query in this table, we will first define indexes for table product_category for desired columns and afterward will add constraints for the same columns. Let’s be clear using the example queries:

To add foreign key on product_id column:

-- Index for table `product_category`

ALTER TABLE `product_category`
 ADD KEY `pro_cat_product_id` (`product_id`);

-- Constraint for table `product_category`

ALTER TABLE `product_category`
ADD CONSTRAINT `pro_cat_product_id` FOREIGN KEY (`product_id`) 
 REFERENCES `products` (`product_id`);

So here we first defined index ‘pro_cat_product_id‘ on ‘product_id‘ field/column and later added foreign key constraint from ‘product_id‘ column of referring table products. We can use the same concept for ‘category_id‘ field as well.

To add foreign key on category_id column:

-- Index for table `product_category`

ALTER TABLE `product_category`
 ADD KEY `pro_cat_category_id` (`category_id`);

-- Constraint for table `product_category`

ALTER TABLE `product_category`
ADD CONSTRAINT `pro_cat_category_id` FOREIGN KEY (`category_id`) 
 REFERENCES `category` (`category_id`);

Apply foreign key in phpMyAdmin using database query required you to keep two things in our mind:

1. All the tables type should be ‘InnoDB‘ because MySQL only supports foreign key constraints only on ‘InnoDB‘ tables.
2. All the associated fields in both referred and referring tables should have defined ‘indexes’ i.e.
   referred table: products => field: ‘product_id‘ (primary key)
   referred table: category => field: ‘category_id‘ (primary key)
   referring table: product_category => field: ‘product_id‘, ‘category_id‘

Now you have a better explanation and you can use either way, graphical as described in past article or using database query as mentioned here. Let me know which method do you prefer