INDEX Command

Creates an index file containing one or more indexes for displaying and accessing table records in a specified order.

Note

You cannot create primary indexes with the INDEX command. If you want to create a primary index using the Visual FoxPro language, use the SQL CREATE TABLE or ALTER TABLE commands.

Caution

Replacing an index key field changes the relative position for the current record in the index. Therefore, use caution if you perform an operation such as REPLACE ALL or SCAN ... ENDSCAN while changing the index for the scope. This also applies to REPLACE operations on indexes created with a FOR clause.

Parameters

eExpression

Specifies an index expression that determines the order in which Visual FoxPro displays and processes records. eExpression can be a simple expression, such as the name of one or more fields from the current table, or a more complex expression containing Visual FoxPro functions, constants, and so on.

Visual FoxPro generates index keys based on eExpression and stores them in an index file that you specify. The index file does not affect the physical order of how records are stored in the table.

Note

For a standalone index (.idx) file, the length of an index key must be between 1 and 100 characters. For a compound index (.cdx) file, the length of an index key must be between 1 and 240 characters.

With some collating sequences, or when using double-byte character set (DBCS) characters, each character in the index expression uses two characters in the index key. In these cases, the length of the index expression is limited to 50 or 120 characters, respectively.

Caution

Avoid using RECNO( ) in the index expression for a table buffered cursor. Because RECNO( ) changes for new records when they are committed by TABLEUPDATE( ), index corruption could occur.

Specifies the name of a standalone index (.idx) file for storing a single index key as generated by eExpression.

Tip

You can override the default index file name extension by including a different extension or by changing the default index extension in the Visual FoxPro configuration file. When creating index files, observe standard Windows rules for naming files, which include long file names.

Specifies the name, or tag, for the index generated by eExpression and stored in a compound index (.cdx) file. Tag names must begin with a letter or an underscore (_) and can consist of any combination of up to 10 letters, digits, or underscore characters.

Note

The number of tags in a .cdx file is limited only by available memory and disk space. If a .cdx file for the table already exists and is open, issuing INDEX with TAGTagName adds a tag to the open .cdx file. If you create an index tag without specifying the name of the index file, Visual FoxPro adds the tag automatically to the table's structural .cdx file.

Specifies the name of a nonstructural compound index (.cdx) file for storing the index, or tag, as generated by eExpression. Omitting this clause creates a structural .cdx file, while including this clause creates a nonstructural .cdx file.

Specifies an order for displaying and accessing records indexed by a compound index (.cdx) file.

ASCENDING specifies an ascending order for displaying and accessing records. By default, Visual FoxPro displays and accesses records in ascending order. However, you can include ASCENDING as a reminder of how records are displayed.

DESCENDING specifies a descending order for displaying and accessing records.

Note

You cannot use DESCENDING when creating standalone index (.idx) files; however, you can specify a descending order for an .idx file using the SET INDEX and SET ORDER commands. For more information, see SET INDEX Command and SET ORDER Command.

[UNIQUE | CANDIDATE]

Creates a unique or candidate index.

UNIQUE stores the matching index key only for the first record that matches the specified index expression.

The index key is stored as the only key in a standalone (.idx) file or as an index tag in a compound index (.cdx) file. Any other index keys for records that match the index expression are excluded from the index file.

Note

Using UNIQUE does not prevent duplicate records from being entered in the table. It only prevents duplicate index keys from being added to the index file. When a duplicate record is changed so that its index key is changed for an active UNIQUE index or index tag, the index or index tag is updated. However, Visual FoxPro cannot display or access the next duplicate record with the original index key until you use the REINDEX command to reindex the file. For more information, see REINDEX Command.

Using UNIQUE is identical to executing SET UNIQUE ON before issuing INDEX or REINDEX. For more information, see SET UNIQUE Command.

The number of index files that you can keep open is limited only by memory and system resources. In Visual FoxPro, the FILES setting in the Windows Config.sys configuration file determines the total number of files you can open.

When specifying an index expression for eExpression, you must specify a valid logical expression that does not evaluate to a null value. If the index expression for a binary index is changed so that it evaluates to a null value, Visual FoxPro generates an error.

When using binary indexes, you cannot use the FOR clause to specify a filter expression or the ASCENDING, DESCENDING, UNIQUE, or CANDIDATE keywords. Visual FoxPro does not support the SET ORDER command when setting to a binary index tag. If you attempt to set order to a binary tag, Visual FoxPro generates an error, and the current order remains at its prior setting. Visual FoxPro does not support SEEK operations or standalone single-key (.idx) indexes with binary indexes.

A structural .cdx file can become dissociated from its table if the index file cannot be located, is deleted, or renamed. When you open a table that has a dissociated structural .cdx file, a dialog box appears. When you click Cancel in the dialog box, the table does not open.

Caution

Clicking Ignore opens the table; however, it also removes the table header flag that indicates that an associated structural (.cdx) file exists.

Tip

To reassaociate the structural .cdx file, issue the following command and specify the name of the table as TableName and the name of the dissociated structural .cdx file as CDXFileName.

To report the number of records indexed during the indexing process, set the SET TALK command to ON. To specify the record interval displayed during indexing, use the SET ODOMETER command. For more information, see SET TALK Command and SET ODOMETER Command.

To obtain information about open index files, use the DISPLAY STATUS Command. DISPLAY STATUS lists the names of all open index files, their types, their index expressions, and the name of the master, or controlling, index file or tag. The number of index files that you can open is limited only by memory and system resources.

Example 1

The following example closes all databases with the CLOSE DATABASES command and opens the Visual FoxPro sample database, TestData.dbc, with the OPEN DATABASE command and the Customer table with the USE command.

The INDEX command creates a standalone index (.idx) file named Complist based on the Company field. The CLEAR command clears the Visual FoxPro main window, and the DISPLAY STATUS command displays information about the index file, Complist.

CLOSE DATABASES
OPEN DATABASE (HOME(2) + 'Data\TestData')
USE Customer
INDEX ON Company TO Complist
CLEAR
DISPLAY STATUS

Example 2

The following example closes all databases with the CLOSE DATABASES command and opens the Visual FoxPro sample database, TestData.dbc, with the OPEN DATABASE command and the Customer table with the USE command.

The INDEX command creates a standalone index (.idx) file named CityComp from a substring of the first five characters of the City field and the first six characters of the Company field using the SUBSTR( ) function. This index file orders records in the table primarily according to the City field and secondarily according to the Company field. Visual FoxPro clears the main Visual FoxPro window with the CLEAR command, and the DISPLAY STATUS command displays information about the index file, CityComp.

Example 3

The following example closes all databases with the CLOSE DATABASES command, and opens the Visual FoxPro sample database, TestData.dbc, with the OPEN DATABASE command and the Customer table with the USE command.

The INDEX commands create a structural compound index (.cdx) file with two tags: one based on the Address field named Address and one based on the Company field named Company. If a structural compound index file was previously created for the table, the two tags are added to the existing file.