Database Reference
In-Depth Information
Using Foreign Keys to Enforce Referential Integrity and
Prevent Mismatches
One feature a database system offers to help you maintain consistency between tables
is the ability to define foreign key relationships. This means you can specify explicitly
in the table definition that a primary key in a parent table (such as the
region_id
column
of the
sales_region
table) is a parent to a key in another table (the
region_id
column
in the
sales_volume
table).
By defining the ID column in the child table as a foreign key to the ID column in the
parent, the database system can enforce certain constraints against illegal operations.
For example, it can prevent you from creating a child row with an ID not present in the
parent or from deleting parent rows without also deleting the corresponding child rows
first. A foreign key implementation may also offer cascaded delete and update: if you
delete or update a parent row, the database engine cascades the effect of the delete or
update to any child tables and automatically deletes or updates the child rows for you.
The InnoDB storage engine in MySQL supports foreign keys and cascaded deletes and
updates.
14.4. Comparing a Table to Itself
Problem
You want to compare rows in a table to other rows in the same table. For example, you
want to find all paintings in your collection by the artist who painted
The Potato Eat‐
ers
. Or you want to know which states listed in the
states
table joined the Union in the
same year as New York. Or you want to know which states did not join the Union in the
same year as any other state.
Solution
Problems that require comparing a table to itself involve an operation known as a self-
join. It's performed much like other joins, except that you must use table aliases so that
you can refer to the same table different ways within the statement.
Discussion
A special case of joining one table to another occurs when both tables are the same. This
is called a self-join. This may be confusing or strange to think about at first, but it's
perfectly legal. You'll likely find yourself using self-joins quite often because they are so
important.
