Visual FoxPro Index Types
Visual FoxPro offers different index types so that you can perform additional tasks with data:
Control whether to permit duplicate values in the fields or index expression specified for generating index keys.
Establish referential integrity in persistent relationships between tables.
Optimize indexes based on logical expressions.
Select and organize a subset of records based on the first occurrence of a specified value.
Optimize search and query operations.
You can use all Visual FoxPro index types except binary indexes to perform query, view, ordering, and SEEK operations on records. You can use all index types to optimize filter conditions and ON and WHERE conditions in SQL SELECT statements.
The following table summarizes index types available in Visual FoxPro.
Visual FoxPro Index Types
| Index type | Description | Number allowed |
|---|---|---|
Primary |
Does not permit duplicate values in the specified fields or expression. |
One per table |
Candidate |
Does not permit duplicate values in the specified fields or expression. |
Many per table |
Regular |
Permits duplicate values in the specified fields or expression. |
Many per table |
Binary |
Indexes records based on a valid and non-null logical expression. |
Many per table |
Unique |
Selects a subset of records based on the first occurrence of a specified value. Included for backward compatibility. |
Many per table |
Primary Indexes
The primary index contains an index key for each record in a table and is the default index when you do not specify any other indexes as the main index for the table. The primary index prohibits duplicate values in the fields or index expression specified for generating index keys; therefore, each index key in the primary index is unique. You can create only one primary index per table.
Note
Primary indexes are an integral part of a table within a database. If you free a table from a database, the primary index is removed. If you specify a primary index on any field that contains duplicate data, Visual FoxPro generates an error.
The primary index is also principally used in the primary or "referenced" table to establish referential integrity in a persistent relationship between tables.
Candidate Indexes
Similar to a primary index, a candidate index contains an index key for each record in a table and prohibits duplicate values in the fields or index expression specified for generating index keys. However, you can create multiple candidate indexes per table. The name "candidate" actually refers to the status of the index; that is, you can create candidate indexes as alternative selections for the primary index.
Note
If you specify a candidate index on any field that contains duplicate data, Visual FoxPro generates an error.
Candidate indexes can be used as either the referenced or referencing index to establish referential integrity in a persistent relationship between tables. For example, in a one-to-many relationship or a one-to-one persistent relationship, you can use candidate and primary indexes to define the "one" side in these relationships.
Regular Indexes
A regular index contains an index key for each record in the table but permits duplicate values in the fields or index expression specified for generating index keys. A regular index is simply an index that is not unique, primary, or candidate. You can create more than one regular index for a table.
You cannot use regular indexes to enforce the uniqueness of the data in those records. You can use a regular index as the "many" side in a one-to-many persistent relationship.
Binary Indexes
A binary, or bitmap, index is used for creating indexes based on logical expressions, for example, indexing deleted records, and is supported for free and database tables. However, binary indexes do not support the following:
Using index expressions that evaluate to null.
Using FOR clause filter expressions.
Changing the order in which records are displayed and processed, for example, by using the ASCENDING, DESCENDING, UNIQUE, or CANDIDATE keywords.
Setting binary indexes as controlling indexes, for example, by using the SET ORDER command.
Performing ordering and seek operations.
A binary index can be significantly smaller than a non-binary index and can improve the speed in maintaining indexes, for example, when using indexes based on deleted records. For more information, see Indexes Based on Deleted Records. Visual FoxPro can often create the Rushmore optimization bitmap faster for a binary index if the number of records returned is more than 3% of the total number of records. However, the Rushmore bitmap might be created slower if the number of records returned is less than 3% of the total number of records. The 3% threshold can decrease as the number of the records in the table increase.
Note
Performance improvements or degradations and binary index sizes might vary with your specific data and application architecture. The numbers used in the example are based on tests using random test data.
You can create binary indexes using the Table Designer or the INDEX command. For more information, see How to: Create Indexes and INDEX Command.
Unique Indexes
Earlier versions of Visual FoxPro included unique indexes. Unique indexes do not actually prevent duplicate values from being entered in the table. The name "unique" actually describes the entries in the index file because the file does not store a particular index key more than once and disregards later occurrences of duplicate index keys. Unique indexes make it possible to specify that only the index key for the first record matching the index expression is added to the index. After a match occurs, no other index keys are added to the index. However, the table can still contain duplicate values.
Tip
If you want to select records in this manner, it is recommended that you create a query or view instead. For more information, see Working with Queries and Working with Views.
See Also
Tasks
How to: Create Indexes (Visual FoxPro)