< Day Day Up > |
I'm sure the topic of this section sounds suspiciously like some form of couples therapy . 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 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 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 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 integrity rule . Using a Value List to Ensure Relational IntegrityWe'd like to add a validation rule to the ShiftAssignment table that says it's not valid to create a record with an nonexistent MilitiaMemberID . The best way to do this in FileMaker is to create a value list containing all the extant MilitiaMemberID numbers , and apply validation that allows only IDs from that list to be used. You'd do that as follows .
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 disallowed . Preserving Referential Integrity During DeletionDeletion 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 the assignments exist, or delete all the shift assignments along with the member himself. These two options are known in database parlance as a restricted delete and a cascading delete , respectively. A restricted delete ensures that parent records with related 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. CAUTION 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.
|
< Day Day Up > |