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.

For more on the broader uses of match fields in FileMaker Pro, see "Relationships as Queries," p. 184, as well as other sections of Chapter 7, "Working with Relationships."

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.

Primary Key Options

Using an automatically entered serial number is one of the simplest ways to create a primary key, but there are other schools of thought. These serial numbers are not globally unique (in other words, unique across multiple tables), or even unique in the context of a single database. As we mentioned earlier, two records in different tables can share the same primary key when this kind of record numbering is used. The odds of any kind of mix-up are slight, but you may want to avoid the concern entirely. Some developers create a key based on complex random criteria, such as the current timestamp or current user ID, for example. A simpler scheme we've seen used involves still using FileMaker's serial numbers but adding a prefix to them so that invoices are numbered INV1, INV2, and so on. FileMaker's serial numbering option accepts text prefixes and suffixes of this sort, and such a scheme can add a useful descriptive dimension to your keys while also better ensuring uniqueness (assuming that you never use duplicate prefixes within one database).

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."

Cardinality in the Relationships Graph

This discussion provides an opportune moment to look again at that crow's-foot that FileMaker so cleverly applied to the Town-TownOfficer relationship created earlier. FileMaker looks at the field definition options to try to determine the cardinality of a relationship. Any field that is either defined to be unique or has an auto-enter serial number is assumed by FileMaker to be the "one" side of a relationship. Lacking either of those characteristics, it's assumed to represent the "many" side. That, in brief, is how FileMaker determines how to draw the cardinality indicators (that is, the crow's-foot) in the Relationships Graph. It's a useful indicator, to be sure, but not bulletproof, and is really just advisory. The cardinality indicator neither creates nor enforces any rules, and it can't be changed from FileMaker's default "guess" value. It simply tells you what FileMaker thinks is going on.

For a discussion of cardinality, see "Relationship Cardinality," p. 138.