Database Reference
In-Depth Information
Industries products…no, the other one.…” As soon as someone mentions an invoice number,
though, everybody knows exactly which invoice to look at. More important, FileMaker
knows which invoice it is, too.
The invoice number is good at identifying an invoice because it has three important charac-
teristics:
▪
It's unique
. No two invoices ever have the same invoice number.
▪
It's unchanging
. Invoice #24601 is #24601 today, and it will be tomorrow, and the next
day, and the next day.
▪
It's consistent
.
Consistent
is a database term that means “never empty.” All invoice re-
cords have an invoice number.
Since it's a unique number, if you're talking about invoice #24601, and your customer is
talking about invoice #24601, there's no question that you're both referring to the same in-
voice. Since it's unchanging, you can go back weeks, months, or even years later and find the
invoice every time. And since it's consistent, you never have lonely invoices hanging out
there without an identifying number. In database terms, the invoice number is called a
key
.
So to sum up: A key is a field whose value uniquely, unchangingly, and consistently identi-
fies one record.
FileMaker doesn't have a special field type for designating a key (although keys are typically
number fields). As far as FileMaker is concerned, any field you use to link one table to an-
other is a key field. Just make sure that you choose a field in which the data follows the rules
you just learned.
Primary and Foreign Keys
When a key field is in the same table as the records it identifies, it's called a
primary key
. In
the Invoice table, the Invoice Number field is the primary key because the invoice number
identifies an invoice record. If you put the invoice number in some other table (like, say, the
Line Items table) it's not a primary key there. Instead, it's called a
foreign key
. Foreign keys
identify records in other tables.
NOTE
The terms “primary” and “foreign” may not seem like a match made in linguistic heaven. Wouldn't
“domestic” and “foreign” make more sense? If those terms don't make sense to you, you're in good
company. FileMaker avoids the terms in its software and its help files. But developers aren't so reti-
cent, and the concepts of primary and foreign keys are extremely important to getting your database
designed properly.
