Defining Referential Integrity Constraints | Advantage Database Server: The Official Guide

Where field-level and record-level constraints define rules concerning the data stored in a single table, referential integrity refers to rules that define relationships between tables. Before continuing with the discussion of referential integrity, it is worthwhile to pause for a moment and consider how tables in an application are related.

In almost all but the simplest application, many tables that make up your database contain related data. For example, you may have one table that contains customer records and another table that contains records of the individual invoices for your customers. These tables are related, in that a given invoice is associated with a specific customer.

This relationship is embodied in the invoice table through the invoice table’s customer ID field. Assuming that the invoice table’s customer ID field is limited to those customer ID values appearing in the customer ID field of the customer table, and that the customer ID field in the customer table is unique for each customer, if you know that value of the invoice table’s customer ID field, you know which customer the invoice is for.

In most applications there are many of these types of relationships. The invoice table records are also likely associated with an employee who made the sale, and that employee will have a record in an employee table. The order may also include a part number or service code, and those will be associated with records in a parts table or a services table, and on and on.

When a primary key index order of one table uses the same key expression as a nonprimary index order of a second table, the fields of the second table’s index order are referred to as a foreign key. Tables that include one or more foreign keys represent associations. The invoice table, for example, represents an association between an invoice and a customer. It also represents an association between an invoice and an employee responsible for the invoice, so long as the invoice table has at least two foreign keys—one for the customer table and one for the employee table.

The relationship between the customer and invoice tables in this example is often called a master-detail relationship, a one-to-many relationship, or a parent-child relationship. These terms are interchangeable, but the term “parent-child relationship” is used by ADS.

Note

The discussion here assumes that you are familiar with the issues related to relational database design. If you are going to be responsible for designing the new tables for an application, and are not familiar with relational database design issues, you should read a book on the topic or engage the services of a qualified consultant before you begin. The success of a database application is greatly influenced by the soundness of the original design.

Referential integrity (RI) refers to index-based rules that define how the related data in your collective tables is managed. RI is useful because the associations between data in the tables of an application are valuable in that, together, they represent important information. For example, if a customer record is deleted from the customer table, and that record was associated with one or more records in the sales table, the deletion of the customer record makes the previously associated sales records less informative. Specifically, you would have a record of a sale, but no information about who made the purchase.

With referential integrity definitions, you can explicitly define what happens when data in related tables is changed. For example, you can use referential integrity to prohibit the deletion of a customer record if that customer is associated with one or more sales in the sales table. To put it another way, referential integrity definitions can prevent loss of data.

The Advantage Data Architect provides you with two mechanisms for defining referential integrity: one is declarative and the other is more visual in nature. Both of these approaches are demonstrated in the following sections.

Defining Referential Integrity Using RI Objects

In order to define a referential integrity relationship, you must specify which two tables participate in the relationship. In addition, you must specify which indexes embody the relationship. Specifically, you must identify which index order of the child table defines the foreign key, and which index order of the parent table defines the primary key.

Obviously, the two index orders—the child table’s foreign key index order and the parent table’s primary index order—must exist prior to your defining the referential integrity relationship. But there is a related issue that is not obvious. You must specifically designate which parent table index order is the primary index. A given table can have only one primary index, and you must explicitly identify which one it is.

You designate a table’s index to be its primary index using the table’s properties dialog box. Using this dialog box is discussed in the preceding section. Setting a table’s primary index is demonstrated here.

Use the following steps to define a referential integrity relationship between the customer (parent) and the invoice (child) tables:

Begin by identifying the primary index of the CUSTOMER table. Right-click the CUSTOMER table’s node and select Properties. Set the Primary Index drop-down list to CUSTOMER ID. (Alternatively, you can expand the CUSTOMER table’s INDEX FILES node in the Advantage Database Manager tree view, right-click the node for the CUSTOMER ID index order, and select Make Primary Index, which displays the Index Structure dialog box. Click OK.) Your table properties dialog box now looks something like that shown in Figure 5-4.

Figure 5-4: The CUSTOMER ID index has been selected as the primary index using the table properties dialog box.
Click OK to close the table properties dialog box.
Next, we need to add some foreign key indexes to the INVOICE table. To do this, expand the INVOICE node under the TABLES node. Expand the INDEX FILES node, then right-click INVOICE.adi and select Add Index. The Index Management dialog box is displayed.
Double-click Customer ID to create a single order expression index on the Customer ID field. Click Create Index.
Now double-click Employee ID to create an index order on this field. Click Create Index again.
Click Close to close the Index Management dialog box. You are now ready to define your referential integrity object.
Right-click RI OBJECTS and select Create.
Set Enter Object Name to Customer Invoices. Set RI Fail Table Path to c:\Program Files\Extended Systems\Advantage\ADSBook\RIFAIL.ADT. The RI fail table is described later in this section. Click Continue to display the Parent Table/Primary Key dialog box.
Set Database Tables to CUSTOMER. The Primary Index field is automatically set to the designated primary index, Customer ID. Click Continue to display the Child Table/Foreign Index dialog box.
Set Database Tables to INVOICE. Set Foreign Index to Customer ID.
Click Continue to advance to the Update/Delete Rules dialog box. Set Update Rule to Cascade. Set Delete Rule to Restrict. (The update and delete rules are described in the next section.)
Click Finish to complete the RI definition. The Advantage Data Architect responds by displaying the RI definition using an ER (entity-relationship) diagram, as shown in Figure 5-5.

Figure 5-5: The newly defined RI object is displayed in an ER diagram.

When you complete this definition, the RI rules will be applied to the table. If the child table contains data when these rules are applied, depending on the specific rules that you applied, there is a possibility that one or more records in the child table violate the RI rules.

If one or more records of the child table violate your RI rules, what happens depends on whether or not you defined an RI fail table path. If you did not specify an RI fail table path, and records exist in the child table that don’t have a corresponding primary key, a dialog box reports that the RI object cannot be created and the Advantage Database Architect (ARC) will ask you if you want to see the first offending record.

If you defined an RI fail table, and one or more records of the child table violate the RI rule you defined, those records are deleted from the child table and placed into the RI fail table. In the preceding steps, you set the RI fail table to RIFAIL.ADT. This table, if created, will be saved to the default directory. Because of the way that the DemoDictionary data dictionary was configured, this directory is the same directory in which the DemoDictionary.ADD file is stored.

Tip

After you have applied your RI rules, check to see if any records have been placed in the RI fail table. If records were added to the RI fail table, you should inspect them to see if they can be corrected and returned to the child table. In addition, since an RI fail table will be overwritten with each RI rule creation, it is a good idea to specify a different RI fail table for each RI rule you create.