Database Reference
In-Depth Information
Figure 5-9. These two relationships look similar, but they show how a simple mistake can cause big
headaches. In the top example, the primary key in the Invoices table matches a foreign key (Invoice
Number) in the Line Items table. This arrangement lets one invoice have many line items because
the same invoice number value can be in the foreign key field in many line item records. But in the
bottom example, a foreign key in the Invoice table is related to the Line Items table's primary key
(Line Item ID), which would let each invoice have just one line item.
Think about what a primary key means. Each primary key value identifies one, and
only one
,
record in the table. If you were to build a relationship between the Invoice and Line Items
tables based on a Line Item ID key, then each invoice could have only one line item. If it had
two, you'd have two line items with the same line item ID, and it would no longer be unique.
When you have a one-to-many relationship, you have to put the foreign key in the table on
the
many
side. Luckily, keeping this information straight in your head is a breeze. Since for-
eign keys belong on the to-many side of a relationship, just remember this rule: When you
