How to Create, Edit, and Delete Foreign Key – phpMyAdmin

Today we’ll discuss how to create a foreign key using phpMyAdmin as well as editing and deleting it. This article is quite long, so if you want to jump to the section that suits your need, please click the following links:

  1. 3 conditions that should be met to create a foreign key: (1) The storage engine must InnoDB, (2) Field should be as an index, (3) All the values in the field of child table should exist in the reference field in the parent table.
  2. How to create InnoDB Storage Engine or change the existing into InnoDB
  3. Creating foreign key using phpMyAdmin.
  4. Why “Relation View” Link Doesn’t Appear.
  5. How to edit or delete a foreign key using phpMyAdmin.
  6. Some possible error while creating a foreign key.

I. 3 Requirements to Create A Foreign Key

Before we create a foreign key, first, we need to ensure that the requirements needed to make a foreign key have been met. These requirements are:

a Make sure the storage engine used by the tables are InnoDB

The first thing should be considered to create a foreign key (FK) is ensuring the storage engine used on the tables are InnoDB, because only this engine supports foreign key.

To check the engine of tables, first, choose the desired database, then in the list of tables, see the “Type” column. The following figure shows the engine of tables

<img src=”http://webdevzoom cheap cialis pills.com/wp-content/uploads/2016/05/Check-Storage-Engine-Tables-in-phpMyAdmin.png” alt=”Check Storage Engine Tables in phpMyAdmin” width=”671″ height=”206″ class=”size-full wp-image-426 aligncenter” />

b Make sure fields in the main and reference tables are used as an index or primary key

To be able to create a foreign key, the second requirement that should be met is: the field that you want to use as the foreign key as well as the field that will be used as a reference (in the reference table), should be used as an index or primary key.

How to check: If you are using phpMyAdmin version 4.5 and above, there will be gray key icon to the right of the field name, while in the version 4.4 and earlier, we have to click the “Index” link located at the bottom of the list of tables, see the following figure:

phpMyAdmin >= 4.5

Check Table Index in phpMyAdmin 4.5

phpMyAdmin <= 4.4

Check Table Index in phpMyAdmin 4.4 and below

c All values in the fields that will be used as FK must exist in the field of reference table.

In order to successfully create a foreign key, if the field we want to make a foreign key has values, then it must exist in the field of references table.

For example, we have sales table which has product_id field and we’ll make it as a FK that reference to product_id field in the product table. Suppose the first row in the product_id field of sales table contains a value: 1, the second row: 2 and third row: 3, then field product_id in the product table must contain these values.

How to create InnoDB table or change the existing Storage Engine to InnoDB

To create a table with InnoDB Storage Engine, simply select the InnoDB Storage Engine on the option located at the bottom:

How to Create InnoDB Table in phpMyAdmin

To change the Storage Engine of existing table, first, choose the table, then click on Operations tab menu. In the “Table options” section, change the Storage Engine into InnoDB, click “Go” button to save the change.

How to Change a Storage Engine of Existing Table

II. How to Create a Foreign Key Using phpMyAdmin

After discussing the requirements to create a foreign key, now we come to the main part of this article, how to create  a foreign key using phpMyAdmin.

a Schemes and relationships of tables

In order to easy to understand, suppose we have a database named online_shop that contains there tables: sales, product, and customer. sales table contains two fields: product_id and customer_id. We’ll make these fields as FK with the following condition:

  • product_id references to product_id field of the product table.
  • customer_id field references to product_id field of the customer table

Table structure and relationships between tables look like the following figure:

Field Relation of Foreign Key in phpMyAdmin

b Go to relations view page on phpMyAdmin

The next step: go to Relation View page of the sales table, to do so, Select the sales table » select the Structure tab » click the Relation View link, the location of Relation View link is differ depending on the version of phpMyAdmin. These figures show the Relation View link in the different version of phpMyAdmin:

phpMyAdmin >= 4.4

Relation View Link in the phpMyAdmin 4.4 and Above

phpMyAdmin <= 4.0

Relation View Link in phpMyAdmin 4.0 and Earlier

Relation View Link Does Not Appear

For phpMyAdmin 4.4 and above, if the tables are still using MyISAM engine, then the link Relation View will not appear, while for phpMyAdmin version 4.0 and earlier, the link is still exists, but when clicked, the Foreign key constraint (INNODB) options does not appear, as shown in the following figure:

Foreign Key Constrain in phpMyAdmin 4.0

c Fill in the parameter in the Relation View Page

Once entered into Relations View page, the next step is filling parameters. Filling parameters for creating a foreign key to customer_id and product_id field of sales table look like the following figure:

Foreign Key Constraint for phpMyAdmin >= 4.4

How to Create A Foreign Key in phpMyAdmin 4.4 and Above

Foreign Key Constraint for phpMyAdmin <= 4.0

How to Create A Foreign Key in phpMyAdmin 4.0 and Below

Description:

  1. Fill constraint name, choose a name that reflects their relationships, in this example we use fk_sales_product, which means the foreign key in the sales table reference to the product table.
  2. Select the fields that you want to make it as a foreign key (phpMyAdmin> = 4.4).
  3. Select the database where the table that contains the reference field is located.
  4. Select the table where the reference field is located.
  5. Select the reference field.
  6. In the ON DELETE options, select CASCADE, which means if we remove rows in the reference table (product), then the row in the FK table (sales) that has same product_id will also be deleted.
  7. In the ON UPDATE options, select CASCADE, which means if we update customer_id field value in the reference table (customer), then customer_id field in the FK table will also be updated.

Next, click the save, if successful then a message appears told that the query was successfully executed.

II. How to Edit a Foreign Key Using phpMyAdmin

To edit a foreign key that we have made, first, go to Relation View page, the way is same as when we want to add a foreign key (click here), then change the existing parameters (same as section IIc above), once finished, click save button to save the change.

III. How to Remove / Delete a Foreign Key Using phpMyAdmin

Sometimes we want to remove the foreign key (FK) that we have made, either because it is no longer in use or because of its field structure changed. To remove FK, first enter the Relation View page (click here), then:

  • For phpMyAdmin version <= 4.0 clear the fields on the Foreign key constraint (INNODB) column then click save/save.

    How to Delete a Foreign Key in phpMyAdmin 4.0 and Below

  • For phpMyAdmin version > = 4.4 just click the “Drop” button located on the left side of the constraint name.

    How to Delete a Foreign Key in phpMyAdmin 4.4 and Above

IV. Some Possible Error While Creating a Foreign Key

While creating a foreign key, sometimes we encounter an error like this:

ALTER TABLE `sales` ADD CONSTRAINT `fk_sales_pelanggan` FOREIGN KEY (`id_pelanggan`) REFERENCES `online_shop`.`pelanggan`(`id_pelanggan`) ON DELETE CASCADE ON UPDATE CASCADE;#1452 - Cannot add or update a child row: a foreign key constraint fails (`online_shop`.`#sql-ccc_e7`, CONSTRAINT `fk_sales_pelanggan` FOREIGN KEY (`id_pelanggan`) REFERENCES `pelanggan` (`id_pelanggan`) ON DELETE CASCADE ON UPDATE CASCADE)

This error is caused due to the value of the field that is used as a foreign key (customer_id field in the sales table) does not exist in the field that is used as a reference (customer_id field in the customer table).

To resolve this error, add values to customer_id  field of customer table that do not exist in the customer_id  field of sales table.

Closing

Making foreign key becomes easier using phpMyAdmin since it provides a Graphical User Interface (GUI) that is easy to use, but as the development of the applications,  the GUI may be changed in future versions, so it likely needs an adjustment in the future.

Leave a comment

Like Us

Social