The Relation View

After the installation of the linked-tables infrastructure, there are now more options available in the Database view and the Table view. We will now examine a new link in the Table view: Relation view. This view is used to:

• Define the relations of the current table to other tables

• Choose the display field

Since our goal here is to create a relation between the books table (which contains the author ID) and the authors table (which describes each author by an ID), we start on the Table view for the books table and click the Relation view link.

Internal phpMyAdmin Relations

Since the books table is in MyISAM format, we see the following screen (otherwise, the display would be different, as explained in the InnoDB Relations section later):

Links to-

Internal relations isbn T]

title Tj pagecount p ~T\

author id T]

language p T]

description T]

coverphoto p T]

genre T]

datepublished p ~T\

stamp T]

Choose field to display: T]

This screen allows us to create Internal relations (stored in the pma_relation table), because MySQL itself does not have any relational notion for MyISAM tables. The double-dash (-- ) characters indicate that there are no relations (links) to any foreign table.

Defining the Relation

We can relate each field of the books table to a field in another table (or in the same table, because self-referencing relations are sometimes necessary). The interface finds the unique and non-unique keys in all tables of the same database and presents the keys in drop-down lists. The appropriate choice here is to select for the author_id field the corresponding author_id field from the authors table. This is also called defining the foreign key.

rLinks to-

Internal relations isbn title page_count authorjd language description cover_photo genre date_published stamp

authors->authorjd ^

We then click Save, and the definition is saved in phpMyAdmin's infrastructure. To remove the relation, we just come back to the screen, select the double-dash choice, and hit Save.

Defining the Display Field

The primary key of our authors table is the author_id, which is a unique number that we made up just for key purposes. Another field in our table represents the authors: the name. It would be interesting to see the author's name as an informative description of each row of the books table. This is the purpose of the display field. We should normally define a display field for each table that participates in a relation as a foreign table.

We will see how this information is displayed in the Benefits of the Defined Relations section. We now go to the Relation view for the authors table (which is the foreign table in this case) and specify the display field. We choose author_name as the display field and click Save:

$ Server: localhost ► # Database: dbbook ► ■ Table: authors

■ Browse e£ Structure s0SQL

/Search

5-i Insert

■ Export ■Import M Operations H Empty HDrop

Links to-

Internal relations authorid P jj]

authorname P jj]

phone P zi

Links to-

Internal relations authorid P jj]

authorname P jj]

phone P zi

Choose field to display: l.authouiame hj

phone |

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

The definition of this relation is now done. Note that, although we did not relate any of the fields in the authors 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.

We will discuss the benefits of having defined this relation in a later section, but first, we will see what happens if our tables are in the InnoDB storage engine.

InnoDB Relations

The InnoDB storage engine offers us a foreign key system. To try it, we will first switch our books and authors tables to the InnoDB storage engine. We can do this from the Operations sub-page in the Table view. We start by doing this for the authors table:

A problem might arise when changing the storage engine of books table to InnoDB. We have a full-text index in this table, and some versions of MySQL do not support it for the InnoDB engine. We have to remove the full-text index if we receive the following error message:

Server: localhost > & Database: dbbook > o Table: books

Error_

SQL query: j

O ALTER TABLE 'books' ENGINE = innodb MySQL said: m

#1214 - The used table type doesn't support FULLTEXT indexes

To get rid of this error message, we go back to Structure for the books table and remove the full-text index on the description field. While we are on this screen, let's also remove the combined index we created on author_id and language. This is because we want to see the consequences of a missing index later in this chapter. At this point we are able to switch the books table to InnoDB.

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

Opening the books table and entering the Relation view now displays a different page:

^ Server: localhost ► ^ Database: dbbook ► |m| Table: books |f|Browse [rfj1 Structure .^SOI y StMnh jfclnsert fllyExport ^Import ^Operations [ffEmpty ^Drop r Links to-

Internal relations InnoDB

r Links to-

Internal relations InnoDB

isbn

1 J

| - jJ ON DELETE | -

ON UPDATE | -

J

title

1 J

| - j^J ON DELETE | -

_J ON UPDATE | -

J

page_count

1 J

No index defined!

authorjd

| authors-> authorjd

No index defined!

language

1 J

No index defined!

description

1 J

No index defined!

couer_photo

1 J

No index defined!

genre

1 J

No index defined!

date_published

1 J

No index defined!

stamp

1 J

No index defined!

This page tells us that:

• We have an internal relation defined for author_id to the authors table.

• We don't yet have any InnoDB relations defined.

• We will be able to remove the internal relation, when the same relation has been defined in InnoDB. This message can be seen when moving the mouse over the small bulb light. In fact, phpMyAdmin advises us that the internal relation is not necessary when it also exists in InnoDB, so it would be better to remove it.

• ON DELETE and ON UPDATE options are available for InnoDB relations.

The page might also tell us that our MySQL version is not up to date. (It needs to be 4.0.13 or later.) If we have a version prior to 4.0.13, we won't be able to remove a relation defined in InnoDB, due to a lack of support for the alter table ... drop foreign key statement. This is why phpMyAdmin could be giving us this friendly (and crucial!) advice.

In the possible choices for the related key, we see the keys defined in all InnoDB tables of the same database. (Creating a cross-database relation is currently not supported in phpMyAdmin.) We even see the keys defined in the current table, because self-referring relations are possible. We now remove the internal relation for the author_id field and hit Save. We would like to add an InnoDB-type relation for the author_id field, but we cannot - we see the No index defined! message on this line. This is because foreign key definitions in InnoDB can be done only if both fields are defined as indexes. (There are also other constraints explained in the MySQL manual.)

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

Indexes: (3

Keyname Type Cardinality Action Field

PRIMARY PRIMARY 3 J? X isbn by title INDEX 3 j? X title 30

authorjd INDEX 3 J X authorjd

In the Relation view, we can again try 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 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.

[» If we have not done so already, we should define the I

\ 'display field' for the authors table, as explained in the I

• Internal phpMyAdmin Relations section. I

In the current phpMyAdmin version (2.8.2), we cannot see tables from a different database in order to define a relation to them.

InnoDB Tables without Linked-Tables Infrastructure

Starting with phpMyAdmin 2.6.0, we see the Relation View link on the Structure page of a InnoDB table even though the linked-tables infrastructure is not installed. This brings us to a screen where we can define the foreign keys - here for the books table.

Note that, if we choose this way, the 'display field' for the linked table (authors here) cannot be defined, since it belongs to the phpMyAdmin's infrastructure, so we would lose one of the benefits (seeing the foreign key's associated description).

$ Server: localhost ► ^ Database: dbbook ^ mu Table: books fljBrowse [restructure 5^SQL ^Search Reinsert U Export H Import ^Operations r Links to-

InnoDB

r Links to-

InnoDB

isbn

| jJ ON DELETE | -

_J ON UPDATE _

J

title

| - jJ ON DELETE | -

jJ ON UPDATE | -

J

pagecount

No index defined!

author id

| - jJ ON DELETE | -

jJ ON UPDATE | -

'1

language

No index defined!

description

No index defined!

coverphoto

No index defined!

genre

No index defined!

date_published

No index defined!

stamp

No index defined!

Benefits of the Defined Relations

In this section we will look at the benefits that we can currently test; other benefits will be described in Chapter 13 (The Multi-Table Query Generator) and Chapter 15 (System Documentation). Some other benefits of the linked-tables infrastructure will appear in Chapter 14 (Bookmarks) and Chapter 16 (MIME-Based Transformations).

These benefits are available for both internal and InnoDB relations.

Foreign Key Information

Let's browse the books table. We see that the related key (author_id) is now a link.

"I"—► isbn title page count author id

V J> X 1-2345S7-22-0 Future souvenirs 200 2

J? X 1-234567-89-0 A hundred years of cinema (volume 1) 600 1

r J? X 1-234567-90-0 A hundred years of cinema (volume 2) 600 1

Moving the mouse pointer over any author_id value reveals the author's name (as defined by the display field of the authors table):

T-* isbn title pagecount authorid

r

J

X

1-234567-22-0

Future souvenirs

200

2

r

X

1-234567-89-0

A hundred years of cinema (volume 1)

600

1

r j/ X 1-234567-90-0 A hundred years of cinema (volume 2) 600

r j/ X 1-234567-90-0 A hundred years of cinema (volume 2) 600

Clicking on the author_id brings us to the relevant table, authors, for this specific author:

authorid authorname phone r V X 1 John Smith +01 445-789-1234

The Drop-Down List of Foreign Keys

Going back to the books table, in Insert mode (or in Edit mode), we now see a drop-down list of the possible keys for each field that has a relation defined. The list contains the keys and the description (display field) in both orders: key to display field, and display field to key. This enables us to use the keyboard and type the first letter of either the key or the display field:

[^SPf Only the key (in this case 1) will be stored in the books table. The display field is only there 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 browseable foreign-table window.

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

$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, in the order we prefer. Thus, defining $cfg['ForeignKeyDropdownOrder'] to array('id-content') would produce:

We can use one or both of the strings content-id and id-content in the defining array, in the order we prefer. Thus, defining $cfg['ForeignKeyDropdownOrder'] to array('id-content') would produce:

The Browseable Foreign-Table Window

Our current authors tables have very few entries - two in fact. Thus, to illustrate this mechanism we will set the $cfg['ForeignKeyMaxLimit'] to an artificially low number, 1. Now in Insert mode for the books table, we see a small table-shaped icon for author_id, as shown in the screenshot that follows:

This icon opens another window presenting the values of the table authors and a Search input field. On the left, the values are sorted by key value (here, the author_id column), and on the right, they are sorted by description. We have added a third author to better see the difference in sorting:

Choosing one of the values (by clicking either a key value or a description) closes this window and brings the value back to the software_id column.

Referential Integrity Checks

We discussed the Operations sub-page and its Table maintenance section in Chapter 10. If we have defined an internal relation for the authors table (a non-InnoDB table), a new choice appears for the books table: Check referential integrity:

Table maintenance

Check referential integrity:

A link (here, author_id -> authors.author_id) appears for each defined relation, and clicking it starts a verification. For each row, the presence of the corresponding key in the foreign table is verified, and any errors are reported. If the resulting page reports zero rows, this is good news!

This operation exists, because for non-InnoDB tables, MySQL does not enforce referential integrity, and neither does phpMyAdmin. It is perfectly possible, for example, to import data in the books table with invalid values for author_id.

Automatic Updates of Metadata 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 it also in the metadata for the relation. The same thing happens when a column or a table is dropped.

Metadata should be manually maintained in case a change in the structure is done from outside phpMyAdmin.

Column-Commenting

Before MySQL 4.1, the MySQL structure itself does not support adding comments to a column. Thanks to phpMyAdmin's metadata, we can nevertheless comment columns. Since MySQL 4.1, native column commenting is supported. The good news is that for any MySQL version, column commenting via phpMyAdmin is always accessed via the Structure page by editing each field's structure. In the following example, we need to comment three columns, so we choose them and click the pencil icon:

Field Type Collation

F isbn varchar(25) Iatin1_swedish_ci r title varchar(IOO) Iatin1_swedish_ci

F page_count int(11) F authorjd int(11)

r language char(2) Iatin1_swedish_ci r description text Iatin1_swedish_ci r cover_photo blob r genre setfFantasy1, Iatin1_swedish_ci

l~ date_published datetime r stamp timestamp t_ Check All / Uncheck All With selected: H ? }

Change

S> Print view ^ Relation view .Pi Propose table structbreTi/

To obtain the next panel as seen here, we are working in vertical mode by setting

$cfg['DefaultPropDisplay'] to 'vertical'. We enter the following comments:

• page_count: approximate

• author_id: cf authors table

Then we click Save.

These comments appear at various places - for example, in the export file (see Chapter 7), on the PDF relational schema (see Chapter 15), and in the Browse mode:

isbn book number title page_count authorjd approximate cf authors table r S X 1-234567-22-0 Future 200 2

souvenirs

If we do not want the comments to appear in Browse mode, we can set $cfg['ShowB rowseComments'] to FALSE. (It is TRUE by default.)

Column comments also appear as a tool tip in the Structure page, and column names are underlined with dashes. To deactivate this behavior, we can set $cfg['ShowProp ertyComments'] to false. (This one is also true by default.)

Automatic Migration

Whenever phpMyAdmin detects that column comments were stored in its metadata and that we are using MySQL 4.1.2 or a later version, it automatically migrates these column comments to the native MySQL column comments.

Summary

In this chapter, we covered the installation of the necessary infrastructure for keeping special metadata (data about tables), and learned how to define relations between both InnoDB and non-InnoDB tables. We also examined the modified behaviour of phpMyAdmin when relations are present, foreign keys, getting information from the table, and column-commenting.

Was this article helpful?

+1 0

Responses

  • nathaniel
    How internal relation in phpmyadmin?
    9 years ago
  • Jasmine
    What is an internal relation INNODB?
    9 years ago
  • hana rezene
    What is internal relation INNODB MYSQL?
    9 years ago
  • sméagol
    How to delete relation in phpmyadmin?
    9 years ago
  • demsas
    What is InnoDB relations in phpmyadmin?
    9 years ago
  • daisy graham
    How we can make the foriegn key in phpmyadmin?
    9 years ago
  • dylan christie
    Why is there two sets of numbers to choose from when picking an id in phpmyadmin with innodb?
    9 years ago
  • myrtle
    How to add realtion to pms_relation table?
    9 years ago
  • spartaco
    Do not delete records if field are in relationship with other table phpmyadmin?
    9 years ago
  • peony
    What are internal relations in phpmyadmin relation view?
    8 years ago

Post a comment