If the book table is in MyISAM format, we see the following screen (otherwise, the display would be different, as explained in the Defining foreign key relations section later):

This screen allows us to create Internal relation (stored in the pma_relation table) as MySQL itself does not have any relational notion for MyISAM tables. The empty drop-down list next to each column indicates that there are no relations (links) to any foreign table.

Defining the relation

We can relate each column of the book table to a column in another table (or in the same table because self-referencing relations are sometimes necessary). The interface finds both the unique and the non-unique keys in all the tables of the same database, and presents the keys in drop-down lists. (Creating internal relations to other databases from the interface is not currently supported.) The appropriate choice for the author_id column is to select the corresponding id column from the author table.

We then click on Save, and the definition is saved in phpMyAdmin's configuration storage. To remove the relation, we would just come back to the screen, select the empty choice, and click on Save.

Defining the display column

The primary key of our author table is the id, which is a unique number that we made up for key purposes. The author's name is the natural way to refer to an author. It would be interesting to see the author's name when browsing the book table. This is the purpose of the display column. We should normally define a display column for each table that participates in a relation as a foreign table.

We will see how this information is displayed in the Benefiting from the defined relations section. We now go to the Relation view for the author table (which is the foreign table in this case) and specify the display column. We choose name as the display column and click on Save, as shown in the following screenshot:

Note

phpMyAdmin offers to define only one display column for a table, and this column is used in all the relations where this table is used as a foreign table.

The definition of this relation is now done. Although we did not relate any of the columns in the author table to another table, it can be done. For example, we could have a country code in this table and could create a relation to the country code of a country table.

For now, we will see what happens if our tables are under the control of the InnoDB or PBXT storage engine.

The InnoDB and PBXT storage engines offer us a native foreign key system.

For this exercise, our book and author tables must be under the InnoDB storage engine. We can do this from the Operations page in the Table view.

Another step is necessary in order to see the consequences of a missing index during the exercise. We go back to the Structure for the book table and remove the combined index we created on author_id and language columns.

The foreign key system in InnoDB maintains integrity between the related tables. Hence, we cannot add a non-existent author ID to the book table. In addition, actions are programmable when DELETE or UPDATE operations are performed on the master table (in our case, book).

Opening the book table on its Structure page and entering the Relation view, now displays a different page:

This page provides us the following information:

In the possible choices for the related key, we see the keys defined in all the InnoDB tables of the same database. (Creating a cross-database relation is currently not supported in phpMyAdmin.) The keys defined in the current table are also shown, as self-referring relations are possible. Let us remove the internal relation for the author_id column and click on Save. Our goal is to add an InnoDB-type relation for the author_id column, but it's not possible as the No index defined! message appears on this line. This is because foreign key definitions in InnoDB or PBXT can be done only if both the columns have indexes.

Thus, we come back to the Structure page for the book table and add an ordinary (non-unique) index to the author_id column producing the following screen:

In the Relation view, we can try again to add the relation we wanted; it works this time!

We can also set some actions with the ON DELETE and ON UPDATE options. For example, ON DELETE CASCADE would make MySQL automatically delete all the rows in the related (foreign) table when the corresponding row is deleted from the parent table. This would be useful, for example, when the parent table is invoices, and the foreign table is invoice‑items. These options are supported natively by MySQL, so deleting outside of phpMyAdmin would cause the delete cascade.