Multiple-field indexes

It is possible in Ability Database to create multiple-field indexes. These are combinations of two or more table fields that together are unique. This is useful when you want each field in the multiple-field index to be able to hold duplicate data, though you don't want all the fields together to hold duplicate combinations of data.

For example, think of a field that contains orders and one that contains products. You want to be able to duplicate the order number, for there may be several products on any one order. Likewise one product may be attached to more than one order. What you don't want is that a particular product and order number together occur more than once, that is, that more than one record contains a duplicate combination on these fields.

To create a multiple-field index you need to be in the "Format Table" dialog (for an existing table) or the "New table" dialog (when creating a new table). See Creating fields in a database.

To create a multiple-field index for an existing table, follow these steps:

  1. Open the table and select the Table command from the Format menu (alternatively, right- click on the table name in Database Manager and select Format). The "Format Table" dialog is opened.

  2. Click on the Index button to go to the index page.

  3. Click in the first empty row in the Table Indexes box.

  4. A multiple-field index for two fields is defined by two rows of the Table Indexes box:

  1. Next, click again anywhere in the first row. This will activate the Primary Key and Unique checkboxes. You can set the multiple-field index as either a primary key in its own right (check Primary Key) or as a unique index (check Unique). Whichever you choose, you can have duplicate values in each of the fields but not a duplicate combination of values. With Primary Key you are not allowed to have blanks in any of the fields of the Multiple-field Index. With Unique you are allowed to have duplicate blanks for either field or both in any row.

Although it is unlikely that you will need to create a multiple-field index with more than two fields, it is possible to add more fields as required. Again, with a three-field or higher multiple-field index, it is only the first field that is given a name in Index name.