Working with Keys and Match Fields

You should remember from Chapter 5 that keys are table fieldsfields that are essential elements in forming the relational structure of a multitable system. FileMaker takes a somewhat broader view of keys, as you'll see, and for that reason these fields are referred to as match fields when you're working in a FileMaker context. A match field in FileMaker is any field that participates in a relationship between two FileMaker tables. Primary keys and foreign keys fit this definition, of course, but so do a number of other types of fields that are explored more in the next chapter.

Key fields (which form the structural backbone of the system) need to play by some special rulesespecially primary keys. Consider the current example, the Town database system, and consider the __kp_TownID field in the Town table. This field has been identified as the primary key for the Town table. To play the role of primary key, there are a few rules the field has to follow. In the first place, the value in it has to be unique within the given table. In the example, this means that no two towns should share the same __kp_TownID (though it's fine if there's a town official with an ID of 27, as well as a town with an ID of 27they're in two different tables, so you won't get them mixed up). The reasons for this are fairly obvious: A town ID isn't much use if two towns can share a single town ID; we'd have no way to identify one single town uniquely. And by the same token, we never want the __kp_TownID field to be empty. FileMaker helps us work within these constraints.

To make a field suitable for use as a primary key, use FileMaker's field options to add some important restrictions to the field definition. You do this in the Options dialog that's available when you have a field selected in the Define Database dialog.

On the Auto-Enter tab of the Options dialog, click the Serial Number check box (see Figure 6.6). (Leave the specific serial number options alone for now.) This instructs FileMaker to enter a new, unique number into the field every time a record is created, starting at whatever number you specify and going as high as necessary.

Figure 6.6. Use a serial numbering auto-entry option to populate a primary key field.

Click the Validation tab, and then click to check the Not Empty and Unique Value check boxes, found in the Require section. This ensures that the field follows the earlier criteria for a good primary key field: never empty, always unique. Lastly, in the upper portion of the box, uncheck the choice that says Allow User to Override During Data Entry. With that box checked, the user could enter his own data values in the field, possibly breaking the established uniqueness rules, or creating incorrect associations between records. You certainly don't want this to be possible. Figure 6.7 shows the Validation tab in use.

Figure 6.7. These validation options are appropriate for a primary key field.

We consider these settings to be essential for any field used as a primary key. For a foreign key, the constraints are less severe. Consider the _kf_TownID field in the TownOfficer table. First, there doesn't need to be a uniqueness constraint. For example, many town officers should be permitted to have the same _kf_TownID. And the _kf_TownID in the TownOfficer table shouldn't be a sequential serial number, either. That's a characteristic of a primary key. The main thing is that it not be empty. So you can simply apply the "not empty" validation rule to a foreign key field and leave it at that.

Note

There's another important constraint you may want to place on a foreign key field. It's called a referential integrity rule, and it's discussed later in this chapter in the section "Relational Integrity."