How to view and create relationships in phpMyAdmin

Using data table relationships reduces the risk of errors and increases the reliability of the application. In the next article, xthemes will show you how to view and create relationships in phpMyAdmin with detailed steps.

Learn about phpMyAdmin relationships

phpMyAdmin allows relationships (such as foreign keys) to use MySQL (InnoDB) native methods where available. In addition, relationships reuse special features specific to phpMyAdmin as needed. There are two ways to edit relationships in phpMyAdmin including relationship perspective and drag-and-drop designer. These two methods will be presented in the following content.

Technical information

Currently, only MySQL’s InnoDB table type supports relationships. Using this table, phpMyAdmin will create InnoDB relationships that MySQL will enforce in any application that accesses the database. In the case of using a data table of a different type, phpMyAdmin only enforces internal relationships and those relationships are not applied to any other applications.

NVME VPS high-speed service program launch

Perspective on Relationships

To see the relationship (relation view), you must first do the right thing ((pmadb|pmadb)). After the setup is complete, you select the item “structure” of the table. Below the table definition there will be a link named “View relevance” is displayed. If you click on that link, a new page is displayed that allows you to create a link to another table with any fields.

Only the Primary Keys will be displayed there, so if the field you selected is not visible, you did something wrong at some point. The last menu is also the field that will be used as the name for the record.

Here is an example of a relational view.

Assuming you have categories, links, and a category can contain several links, the table structure will look like this:

• category.category_id (id must be unique)
• category. name
• link.link_id
• link.category_id
• link.type.

Open the mode relationship perspective below the table structure for the table link (link) and for school category_id. Next you choose category.category_id primary record.

You will continue to reload the website, this time the field category_id will be a hyperlink that allows you to click on the appropriate category record. However, all you will see is category_id not the category name. If you want to overcome this drawback, you open the relation view of the table category and select the item name in the bottom menu. After reloading the page again, you hover over the link category_id will see the category value displayed as a legend.

Create a relationship with a MySQL database using phpMyAdmin

In this section, you can create relationships with a MySQL database using phpMyAdmin. In case for some reason you don’t want to use phpMyAdmin, you will find the SQL query methods listed below.

In all cases, using data table relationships will increase fault tolerance and increase application reliability. For example, you can refuse to delete a category from any article that is already connected to it. Or you can delete all posts if you remove the category.

To create a relationship with phpMyAdmin you need to use the tool InnoDB, only this tool supports foreign keys. If you already own MyISAM tables, you can convert them to InnoDB. Also before adding the relation you must add the fields to the index.

In phpMyAdmin, you select a table and then select structure mode (Structure)select a relevant field, and then tap Index.

You have to remember the difference between “Index“and”Unique“. For example, unique can be used for the ID field, where values ​​are not duplicated.

This can also be done in the query statement:

ALTER TABLE `table_name` ADD INDEX ( `field_name` ) ;

Similarly, add a Index for the table with the linked relationship (owner).

Using the SQL statement:

ALTER TABLE `table_name` ADD UNIQUE (`field_name`);

Now only the related tables remain, to perform the relationship you clicked on “Connections” under.

For existing fields, select relationships to external tables and actions (constraints) on field changes.

Using the SQL query statement:

ALTER TABLE `table_name` ADD FOREIGN KEY ( `field_in_table_name_which_need_connect` ) REFERENCES `outer_table_to_connect` (`outer_field`) ON DELETE RESTRICT ON UPDATE RESTRICT ;

Epilogue

Above are all the steps needed to create a relationship in phpMyAdmin, now the tables are joined by foreign keys. Hope the above instructions helped you to solve the problem you are facing.