How to add a foreign key in phpMyAdmin

In SQL database language in general, and in particular in PHPMyAdmin, a foreign key is a field of a table of the database that is depending on the field of another database.


PHPMyAdmin: foreign key, what is it?

In SQL database language in general, and in particular in PHPMyAdmin, a foreign key is a field of a table of the database that is depending on the field of another database.

This means that it is not possible to create an entry in a table referencing via a foreign key another table, if the other table does not contain an entry corresponding to the foreign key being inserted.

For example, this is like linking a bottle and its cork – you can’t use the cork if the bottle isn’t there.

By adding a PHPMyAdmin foreign key to a table, you will make sure that any entry in that table has a direct reference to a unique entry entry of another table. A generally accepted good practice is to create PHPMyAdmin foreign key to reference other tables indexed fields, such as primary keys, unique keys or indexed keys, in order to make sure that a single entry is uniquely referenced by the PHPMyAdmin foreign key created.

PHPMyAdmin foreign key and table relations - Relations — phpMyAdmin 5.1.0-dev documentation

How to set foreign key in phpmyadmin

Adding a foreign key in phpMyAdmin is pretty simple, but the option might be difficult to find.

Open the table in which you want to add the foreign key. Go to the tab Structure, in which you will find a sub menu called Relation View. There, select the column which will hold the foreign key, and the table and column referenced by this key.

As the menus might be pretty difficult to find, refer to below screenshots.

It is also possible to add a foreign key with following SQL code :

ALTER TABLE `table1` ADD FOREIGN KEY (`FK_table2`) REFERENCES `table2` (`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT;

Create foreign key phpmyadmin

Master Website Creation: Enroll Now!

Transform your digital presence with our comprehensive Website Creation course – start your journey to becoming a web expert today!

Enroll Here

Transform your digital presence with our comprehensive Website Creation course – start your journey to becoming a web expert today!

Creating a foreign key allow for several options, including making sure that a column only contains entries existing in another table, possibility to prevent deletion of entries in the other table, or to cascade the deletion to referenced tables.

But one of the most useful options, in the phpMyAdmin interface, is to get a dropdown list with entries of the other table, when inserting entries in a table containing foreign keys.

It is even possible to decide which value is displayed by default from a table, when this is one is referenced by a foreign key in another table, as per below example.

How to use foreign key in PHPMyAdmin

In the foreign keys constraints section of a table, an indexed column can be set as a foreign key, simply using the graphical user interface.

How to remove foreign key in PHPMyAdmin

Open the table in which you want to remove a foreign key in the PHPMyAdmin interface. Go to structure > relation view, and there, click on “DROP” button in the line corresponding to the foreign key you want to delete relationship in PHPMyAdmin.

PHPMyAdmin relation view missing

The relation view is located under the Structure tab name, after a table has been selected from the PHPMyAdmin graphical user interface.

Frequently Asked Questions

How to set foreign key in mySQL phpMyAdmin?
Open the table where you want to add the foreign key. Go to the Structure tab, in which you will find the Relationship View submenu. There, select the column that will contain the foreign key, and the table and column referenced by that key.
How do I create a foreign key in phpMyAdmin?
To create a foreign key in phpMyAdmin, you need to open the table where you want the foreign key, navigate to the Structure tab, and from there, go into the Relation View submenu. You can then select the column that will hold the foreign key and choose the table and column it references.
What steps should be followed to successfully add a foreign key constraint in phpMyAdmin?
To add a foreign key in phpMyAdmin, navigate to the structure tab of the child table, click the Relation view, select the foreign key column, and then specify the referenced table and column. Ensure both tables are InnoDB and the referenced columns are indexed.

Yoann Bierling
About the author - Yoann Bierling
Yoann Bierling is a Web Publishing & Digital Consulting professional, making a global impact through expertise and innovation in technologies. Passionate about empowering individuals and organizations to thrive in the digital age, he is driven to deliver exceptional results and drive growth through educational content creation.

Master Website Creation: Enroll Now!

Transform your digital presence with our comprehensive Website Creation course – start your journey to becoming a web expert today!

Enroll Here

Transform your digital presence with our comprehensive Website Creation course – start your journey to becoming a web expert today!



Comments (12)

 2018-08-19 -  Joan Shaw
この情報を読んで素晴らしい時間を過ごして、良い仕事を続けてください
 2018-08-19 -  Jon Valdez
ขอบคุณมากข้อมูล
 2018-08-19 -  Larry Hart
ง่ายชัดเจนและอธิบายได้ดีขอบคุณมาก
 2018-08-19 -  Kimberly Jenkins
Ótimo site, por favor, continue indo
 2018-08-19 -  Alice Alexander
Eso funcionó bien para mí, no hay necesidad de buscar más
 2018-08-19 -  Tinkeragea
Nagyszerű oldal, kérem folytassa
 2018-08-19 -  randopassionA
안녕하세요, 귀하의 기사를보고 그것이 내 문제를 해결하는 데 도움이 감사합니다.
 2018-08-19 -  mamanoursec
هذا جيد بالنسبة لي ، لا حاجة للنظر إلى أبعد من ذلك
 2018-08-19 -  histologit
Nevaru ticēt, ka beidzot atradu risinājumu, tas jau ilgu laiku bija murgs, tagad tas ir atrisināts
 2018-08-19 -  Pyperermatto
Sekeping maklumat yang besar, terima kasih kerana berkongsi
 2018-08-19 -  tehnikovx
Tidak percaya saya akhirnya mendapati penyelesaian itu, ini adalah mimpi ngeri untuk masa yang lama, kini diselesaikan
 2018-08-19 -  LokubusaS
Vet ikke hvordan du skal takke deg, men det er rent geni, takk

Leave a comment