Relational Integrity

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

  1. Define a new value list, called MemberIDs . 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 MilitiaMemberID field. Choose the Validation tab, and on that screen check the Member of Value List box. For the value list, choose the MemberIDs 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.

    graphics/06fig23.jpg


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


graphics/troubleshooting_icon.jpg

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.


 <  Day Day Up  >  


QUE CORPORATION - Using Filemaker pro X
QUE CORPORATION - Using Filemaker pro X
ISBN: N/A
EAN: N/A
Year: 2003
Pages: 494

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net