After the installation of the phpMyAdmin configuration storage, there are more options available in the Database
view and the Table
view. We will now examine the Relation view link in the Structure page of the Table
view.
This view is used to:
Our goal here is to create a relation between the book
table (which contains the author ID) and the author
table (which describes each author by an ID). We start on the Table
view for the book
table, go to Structure, and click on the Relation view link.
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.
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.
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:
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.
At your choice, the exercises in this section can be accomplished with either InnoDB
or PBXT
storage engines. InnoDB
has been chosen in the text.
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:
author
table. InnoDB
relations are defined yet. InnoDB
. Indeed, hovering over the question mark next to Internal relations displays the following message: An internal relation is not necessary when a corresponding FOREIGN KEY relation exists. So, it will be better to remove it.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.
Other conditions regarding constraints are explained in the MySQL manual. Please refer to http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html.
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.
If we have not done so already, we should define the display column for the author
table, as explained in the Defining the display column section.
We see the Relation view link on the Structure page of an InnoDB
or PBXT
table, even when the configuration storage is not installed. This would bring us to a screen where we could define the foreign keys, in this case for the book
table.
Note that, if we choose this, the display column for the linked table (in this case author)
cannot be defined, as it belongs to phpMyAdmin's configuration storage. Thus, we would lose the benefit of seeing the associated description of the foreign key.
18.191.162.51