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:
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.
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.
Part I: Getting Started with FileMaker 8
Using FileMaker Pro
Defining and Working with Fields
Working with Layouts
Part II: Developing Solutions with FileMaker
Relational Database Design
Working with Multiple Tables
Working with Relationships
Getting Started with Calculations
Getting Started with Scripting
Getting Started with Reporting
Part III: Developer Techniques
Developing for Multiuser Deployment
Advanced Interface Techniques
Advanced Calculation Techniques
Advanced Scripting Techniques
Advanced Portal Techniques
Debugging and Troubleshooting
Converting Systems from Previous Versions of FileMaker Pro
Part IV: Data Integration and Publishing
Importing Data into FileMaker Pro
Exporting Data from FileMaker
Instant Web Publishing
FileMaker and Web Services
Custom Web Publishing
Part V: Deploying a FileMaker Solution
Deploying and Extending FileMaker
FileMaker Server and Server Advanced
Documenting Your FileMaker Solutions