Enforcing Unique Data Table Keys with an Alternate Index

The default set up of an Alternate Index allows the possibility that duplicate Data Table keys are created in the Data Table. This can occur when an Alternate Index is created and the key that is used is not the same as the Data Table key. When data is entered using this Alternate Index, a duplicate Data Table key can be created that while it does not conflict with the key of the Alternate Index, does conflict with the existing Data Table key.

To illustrate this potential conflict, examine the following example shown in Figure 142 and Figure 143.

Assume that no duplicate keys are permitted in either table.

If a user were to update DATAVIEW (see Figure 142) with an Employee Number that already existed in the data, then the update would be rejected.

If a user were to update INDXVIEW (see Figure 143) with a Last Name that already existed in the data, then that update would be rejected.

However, if while using INDXVIEW a user enters data that contains an Employee Number that already exists but with a Last Name that is unique, this update will be accepted and the Data Table now contains an entry with a duplicate Employee Number. A duplicate Data Table key has been created and permitted using the Alternate Index View.

To enforce a unique Data Table key(s) while using an Alternate Index, extra steps need to be taken in defining the Alternate Index View. The definition for the Alternate Index View needs to include not only its own key(s) but also the Data Table key(s) of the Data Table, and the Duplicate Prime Keys field must be set to N. This can be done when creating the Alternate Index View or by editing an existing Alternate Index View:

1. Identify the fields that are key(s) for the Alternate Index View and Data Table key(s) for the Data Table.
2. Edit the field definitions of each of the fields identified in Step 1 and set the Key Field Indicators to the appropriate values (see Table 139).

   There are eight possible options for the definition of the Key Field Indicator, but only four (see Table 162) are relevant to enforcing a unique Data Table key (see Key Field Indicator). By using these options, you can indicate whether a field is a key in the Alternate Index and/or a Data Table key.

   Table 162. Data Table Key Field Indicators for an Alternate Index View

   Option	Description
   P	Indicates that the field is a Data Table key.
   O	Indicates that the field is a Data Table key and a key of the Alternate Index.
   Q	Indicates that the field is both a Data Table key and is to be used as the Dynamic View Suffix.
   A	Indicates that the field is a Data Table key, a key of the Alternate Index. and is to be used as the Dynamic View Suffix.

   In addition to defining the keys of the Alternate Index by identifying the fields that are keys of the Data Table keys, unique Data Table Keys can be enforced.

3. Set the Duplicate Prime Keys field on the Edit Supplementary Information screen, Option 2 – DEFINE VIEW SUPPLMT on the Define Table and View menu, to N.