Create relations in MySQL database using phpmyadmin

After reading this article, you will be able to create relations in MySQL database using phpmyadmin. If for some reasons you don't want to use phpmyadmin, please look at SQL queries below.

So, what reason to store relations in the database? After all, your application also solves this task. Actually, it's all about the restrictions and actions upon changes, which can be set using relations.

For example, you can deny deleting category while any article connected with it. Or remove all articles if category removed. Or set NULL in relation field. In any case, using relations increases fault tolerance and reliability of the application.

You need to use an InnoDB engine to create a relation. Only this engine supports foreign keys. If you have MyISAM tables, you can read how convert it to InnoDB.

Before making tables relations you should add fields to the index:

In phpmyadmin choose a table, choose a structure mode, select a field, which has to be related and click Index.

Note the difference between "Index" and "Unique". Unique can be used, for example, for a id field, where values are not reapeted.

This also can be achieved using SQL-query:

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

Further similar, add an index (in my case unique or primary) for a table which relation linked (owning side).

Using SQL-query:

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

Now left only to relate tables. To do this, click 'Connections' on the bottom side:

For available fields (available only indexed fields) select a relation to external tables and actions(restrictions) on a field changes:

Using SQL-query:

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 ;

That's all. Now tables are connected using foreign key.