I'm sure the topic of this section sounds suspiciously like some form of couples
. Not to fear ”it'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
, speaks to the notion that a database structure, as
in an ERD, also implies certain rules about what can and cannot happen in a database. If you read Chapter 5, you
the concept of
. 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 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
because of data entry error (again, a great reason to use a value list in the way we demonstrated in the previous 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 that you should avoid. There are a couple of things you can do to prevent this. To prevent erroneous entry of a non-existent member ID, you can use field validation. To prevent the creation of orphaned records as a result of deletion, you can use an
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 an nonexistent
. The best way to do this in FileMaker is to create a value list containing all the extant
, and apply validation that allows only IDs from that list to be used. You'd do that as
Define a new value list, called
. You can make it by duplicating the Members list you already created. This
from the earlier list only in that it does not use values from a second field, so that box should
unchecked. (Because the earlier value list sorts based on the
field, it cannot be used to validate based on the contents of the ID field.)
Go to Define Database, edit the ShiftAssignment table, and edit the field options for the
field. Choose the Validation tab, and on that screen check the Member of Value List box. For the value list, choose the
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
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 exist in the MilitiaMember table, you'd get a warning that the action was
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
the deletion on the grounds that the assignments exist,
delete all the shift assignments along with the member himself.
These two options are known in database parlance as a
, respectively. A restricted delete ensures that parent records with
children can't be deleted ”an attempt to do so produces an error. A cascading delete, on the other hand, deletes all the associated shift assignment records along with the member. FileMaker doesn't at this point support restricted delete directly, although the effect can be achieved in other ways.
To add a cascade-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.
Be sure not to check the corresponding box under MilitiaMember. This would have the effect of deleting a member record any time 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.