Doing More with phpMyAdmin (Part 2) - Tangled Relationships (
Page 4 of 7 )
A major cause for complaint amongst developers
working with earlier versions of MySQL was the lack of support for foreign keys.
This lack of support meant that developers needed to hard-wire additional
safeguards into their code to maintain data integrity between tables. Newer
versions of MySQL do include support for foreign keys, but this support is still
fairly new and fails to address users still working with older versions of the
software.
While phpMyAdmin cannot do much to solve this problem, the
developers behind the application have tried to make things a little simpler by
allowing developers to define foreign key relationships between tables at the
phpMyAdmin level, if not the MySQL level. Doing this makes it possible to
enforce the integrity constraints between tables when entering records,
so
long as phpMyAdmin is being used for data entry.
The best way to
understand this is with a simple example. Navigate to the
Structure
option of the
branches table created previously, and scroll down
the page to location the
Relation view hyperlink. Selecting this
link will take you to the section that allows you to define relationships
between tables, associate comments with columns (useful when creating a database
dictionary, explained later in this tutorial) and specify the column to use in
foreign key references.
Here's
what it looks like.
Set the relationship between
the
clients and
branches tables in the
Links
To section, by using the drop-down list to associate the
branches.cid field with the
clients.cid field,
as
seen here.
Next, go to the
Relation view for the
clients table, and tell phpMyAdmin to
show
the cname field in the
Choose Field to display
box.
Now, if you try inserting some data into the
branches
table, phpMyAdmin will, instead of allowing you free-form entry into the
cid field,
provide you with a drop-down selection list of all the
cname values from the
clients table, thus making it
impossible for you to enter an incorrect or non-existent client ID.
Exercising this option has other advantages too. Browse the
branches table, and you'll see that
the
values in the
cid column are clickable; Just click a value and
you'll be transported to the corresponding record in the
clients
table. In fact, just hold your mouse over any of the
cid values and
you will see the corresponding
cname value from the
clients table as a neat little tool tip. Cool, huh?
Once
you set up the remaining relationships between the tables, you'll have a
ready-to-use administration module in a fraction of the time it would have taken
you to code it in regular PHP!