In this section, we will look at the benefits of the defined relations that we can currently test. Other benefits will be described in Chapter 12 and Chapter 15. Additional benefits of the phpMyAdmin configuration storage will appear in Chapter 14, Chapter 16, and Chapter 18.
These benefits are available for both internal and foreign key relations.
Let us browse the book
table. We see that the values of the related key (author_id) are now links. Moving the cursor over any author_id value reveals the author's name (as defined by the display column of the author
table).
Clicking on the author_id brings us to the relevant table —author—
for this specific author:
Instead of viewing the keys, we might prefer to see the display column for all the rows. Going back to the book
table, we can select the Relational display column display option and click on Go. This produces a screen similar to the following screenshot:
We now switch back to viewing the keys by selecting Relational key and clicking on Go.
Displaying the book
table, in Insert mode (or in Edit mode), there is now a drop-down list of the possible keys for each column that has a defined relation. The list contains the keys and the description (display column) in both orders—key to the display column as well as display column to the key. This enables us to use the keyboard and type the first letter of either the key or the display column.
Only the key (in this case 1) will be stored in the book
table. The display column is shown only to assist us.
By default, this drop-down list will appear if there are a maximum of 100 rows in the foreign table. This is controlled by the following parameter:
$cfg['ForeignKeyMaxLimit'] = 100;
For foreign tables bigger than that, a distinct window appears—the foreign-table window (refer to the next section) that can be browsed.
We might prefer to see information differently in the drop-down list. Here, John Smith is the content and 1 is the ID. The default display is controlled by the following line of code:
$cfg['ForeignKeyDropdownOrder'] = array( 'content-id', 'id-content'),
We can use one or both of the strings—content-id
and id-content—in
the defining array and in the order we prefer. Thus, defining $cfg['ForeignKeyDropdownOrder']
to array('id-content')
would produce a list with only those choices:
1 John Smith 2 Maria Sunshine 3 André Smith
Our current author
table has very few entries. Thus, to illustrate this mechanism, we will set the $cfg['ForeignKeyMaxLimit']
to an artificially low number, 1. Now in the Insert mode for the book
table, we see a small table-shaped icon and a Browse foreign values link for author_id column. This icon opens another window, which will present the values of the author
table and a Search input field. On the left, the values are sorted by key value (here, the id column), and on the right, they are sorted by description.
Choosing one of the values (by clicking either a key value or a description) closes this window and brings the value back to the author_id column.
We discussed the Operations page and its Table maintenance section in Chapter 9. For this exercise, we suppose that both the book
and author
tables are not under the control of the InnoDB
or PBXT
storage engine. If we have defined an internal relation for the author
table, a new choice appears for the book
table—Check referential integrity.
A link (author_id -> author.id) appears for each defined relation, and clicking on it starts verification. For each row, the presence of the corresponding key in the foreign table is verified and errors, if any, are reported. If the resulting page reports zero rows, it is good news!
This operation exists because for tables under the storage engines that do not support foreign key natively, neither MySQL nor phpMyAdmin enforces referential integrity. It is perfectly possible, for example, to insert data in the book
table with invalid values for author_id column.
phpMyAdmin keeps the metadata for internal relations synchronized with every change that is made to the tables via phpMyAdmin. For example, renaming a column that is part of a relation would make phpMyAdmin rename this column in the metadata for the relation. This guarantees that an internal relation continues to function, even after a column's name is changed. The same thing happens when a column or table is dropped.
18.118.139.15