You use the update and delete rules of an RI definition to control what kind of changes are permitted to the parent table when associated child records exist, as well as what to do if changes would otherwise disassociate the relationship.
The update rule defines what happens when you want to post a change to a parent table record that has one or more associated child records. Selecting an update rule of Restrict prevents a parent table’s key fields from being changed for any record for which there are related child records. Using our current tables for example, if you use a restrict rule and there are invoices for a particular customer in the invoice table, you cannot change that customer’s Customer ID field.
If you set Update Rule to Cascade, changes made to a parent table’s key fields are propagated to the corresponding fields of existing child records. For example, using a cascade rule, you can change the Customer ID field for customer records, even when there are associated child records. The changed Customer ID field will be applied both to the customer table as well as the associated invoice table records.
Both the Restrict and the Cascade update rules ensure that the parent/child relationship in your tables is maintained. The remaining two options, Set Null and Set Default, permit you to sever the relationship. These rules control what happens to the fields of the child table foreign key when the relationship ends.
If you set Update Rule to Set Null, changing a parent key will cause the associated child table record’s foreign key fields to be set to a null value. For example, if you were to change the Customer ID field in the customer table, any related invoice table records would have their Customer ID fields set to null values. This has the effect of orphaning the child table records.
If you set Update Rule to Set Default, changing a parent key causes the associated child table record’s foreign key to be set to the default value defined using the Fields dialog box. This default value must be a legitimate primary key in the parent table, so although an Update Rule of Set Default will sever the relationship, it will create a new relationship to some other parent.
Delete rules are similar to update rules, but apply when you attempt to delete a parent table record for which there are associated child records. Set Delete Rule to Restrict to prevent a parent record from being deleted if at least one associated child record exists. When this rule is in place, you must first delete any associated child table records before you can successfully delete the parent table record.
Set Delete Rule to Cascade if you want ADS to automatically delete associated child records when a parent record is deleted. Considering the example tables of the DemoDictionary data dictionary, if a cascade delete rule is enforced, deleting a customer would cause the automatic deletion of the invoices for that customer.
Set Delete Rule to Set Null if you want the foreign key of the associated child table records to be set to a null value upon deletion of the parent record. Finally, set Delete Rule to Set Default to set the foreign key of the associated child table records to the default value defined using the Fields dialog box.
When you use update and delete rules other than Restrict, changes or deletions made to the parent table can potentially introduce unique key violations in the child table if the child table’s foreign key field is part of another unique index.
An ER (entity relationship) diagram is a visual tool for representing tables and their relationships. Specifically, it represents the RI links between tables based on primary/foreign key associations.
As you saw in Figure 5-5, an ER diagram is created from your RI object definition. Instead of creating an RI object, you can define your referential integrity relationships using the ER diagram directly. This is demonstrated in the following steps:
Before you can define the RI relationship, you must set the primary index for the EMPLOYEE table. (You already added a foreign key index to the INVOICE table for the Employee ID field in the preceding section.) Begin by expanding the TABLES node of the Advantage Database Manager tree view.
Right-click the EMPLOYEE node and select Properties. Use the table properties dialog box to set Primary Index to Employee Number. Click OK.
You are now ready to define the RI constraint. Start by making sure that the ER diagram is displayed. If you do not see it, expand the RI OBJECTS node and select Customer Invoices.
Now, drag the EMPLOYEE table node (under TABLES) and drop it into the ER diagram. (Drag by left-clicking the employee node, and while keeping the mouse button depressed, move the mouse into the ER diagram area. Drop the EMPLOYEE table by releasing the mouse button once you are over the ER diagram.) Your screen should now look something like that shown in Figure 5-6.
Figure 5-6: The EMPLOYEE table now appears in the ER diagram, although it is not yet part of an RI definition.
Click on the Employee ID Foreign Key index order in the INVOICE table. When you do so, your cursor will appear as though you are performing a drag operation. Now click on the EMPLOYEE NUMBER index order of the EMPLOYEE table. The Advantage Data Architect responds by displaying the Referential Integrity Creation dialog box shown in Figure 5-7.
Figure 5-7: The RI Object Name page of the Referential Integrity Creation dialog box
Set RI Object Name to Employee Sales. Click Continue to display the Update/Delete rules page of the Referential Integrity Creation dialog box, shown in Figure 5-8.
Figure 5-8: The Update/Delete rules page of the Referential Integrity Creation dialog box
Set Update Rule to Cascade and Delete Rule to Restrict. Set the fail table path to c:\Program Files\Extended Systems\Advantage\ADSBook\RIFAIL.ADT. Set Primary Key Violation to You must provide an Employee ID, and leave the Cascade Error field blank. Click Finish.
The ER diagram now displays this new RI definition, with the link between the EMPLOYEE (parent) table’s primary index Employee ID and the INVOICE (child) table’s foreign key index Employee ID.