Relational Integrity

No doubt the topic of this section sounds suspiciously like some form of couples therapy. Not to fearit's a good deal less interesting than that, unless you're a database designer, in which case it's endlessly fascinating.

Relational integrity, also known as referential integrity, speaks to the notion that a database structure, as expressed in an ERD, also implies certain rules about what can and cannot happen in a database. If you read Chapter 5, you encountered the concept of optionality rules. An optionality rule may, for example, assert that an order must have at least one order line item. Or it may assert the more obvious truth that an order line item that doesn't relate to an existing order is an error.

Consider the example of shift assignments again. Suppose that there's a record in the ShiftAssignment table that references a militia member with an ID of 1002, when in fact there is no member with that ID. This could have happened because of data entry error (again, a great reason to use a value list in the way we demonstrated in the preceding section). It could also happen if that member had existed once upon a time and has since been deleted. In that case, without integrity rules to protect against this, the member's assignment records would be left dangling in the ShiftAssignment table. Database analysts usually refer to such records as orphans, and their existence is a violation of referential integrity.

This state of affairs clearly looks like an error you should avoid. There are a couple of things you can do to prevent this problem. To prevent erroneous entry of a nonexistent member ID, you can use field validation. To prevent the creation of orphaned records as a result of deletion, you can use an integrity rule.

Using a Value List to Ensure Relational Integrity

We'd like to add a validation rule to the ShiftAssignment table that says it's not valid to create a record with a nonexistent militia member ID. The best way to do this in FileMaker is to create a value list containing all the extant militia member ID numbers, and apply validation that allows only IDs from that list to be used. You'd do that as explained here:

1. Define a new value list, called MemberID. You can make it by duplicating the Members list you already created. This differs from the earlier list only in that it does not use values from a second field, so that box should remain unchecked. (Because the earlier value list sorts based on the Name field, it cannot be used to validate based on the contents of the ID field.)
2. Go to Define Database, edit the ShiftAssignment table, and edit the field options for the _kf_MilitiaMemberID field. Choose the Validation tab, and on that screen check the Member of Value List box. For the value list, choose the MemberID value list you created earlier to help with data entry. While you're here, you might as well also stipulate that the field can't be empty and that the user may not override these restrictions. You can also provide a custom message if the validation should fail. These options are shown in Figure 6.23.

Figure 6.23. Use FileMaker's validation options to enforce a referential integrity rule between two tables.

Now, if you were to try to enter a member ID that didn't already exist in the MilitiaMember table, you'd get a warning that the action was disallowed.

Preserving Referential Integrity During Deletion

Deletion is another pitfall if you're picky about keeping your database consistent. What happens if you want to delete a member, and he already has shift assignments? Well, you have two choices: either forbid the deletion on the grounds that related assignments exist, or delete all the shift assignments along with the member himself.

These two options are known in database parlance as restricted delete and cascading delete, respectively. A restricted delete ensures that parent records with related children can't be deletedan attempt to do so produces an error. A cascading delete, on the other hand, deletes all the associated child records along with the parent record. FileMaker doesn't at this point support restricted delete directly, although the effect can be achieved in other ways.

FileMaker does, though, support cascading delete directly. To add a cascading delete rule to a relationship, simply edit the MilitiaMember-ShiftAssignment relationship in the Relationships Graph. On the ShiftAssignment side of the dialog box, look for a check box that says Delete Related Records in This Table When a Record Is Deleted in the Other Table.

Caution

Be sure not to check the corresponding box under MilitiaMember. This would have the effect of deleting a member record anytime a corresponding shift assignment was deleted. This is the wrong direction in which to cascade! Also be aware that cascade effects are cumulative. If you define multiple cascade-deletion rules in a system, a single deletion can sweep across multiple tables. Pay careful attention to the details of this feature until you're comfortable working with it. As with other mass-update operations in FileMaker, such as Replace Field Contents, or a data import, there is no way to undo such deletions.

It's possible to configure FileMaker's security privileges in a way that interferes with the enforcement of integrity rules. See "Accidental Delete Restrictions" in the "Troubleshooting" section at the end of this chapter.