How to: Create Indexes (Visual FoxPro)

  • Article

You can create an index for a table when you define a field for a table or after you create the table. When you create an index, you supply an expression, which can contain table field names, that specifies how table records should be organized. You can also specify a filter expression so that Visual FoxPro adds only those records that meet the filter criteria to the index.

Tip

Indexes are easy to create, but they impact performance so use restraint when creating them. You do not need to define an index for every field. Indexes that you rarely use can slow performance.

You can create an index using the Visual FoxPro Table Designer or programmatically. For information about using Visual FoxPro commands to create indexes, see Creating Indexes Programmatically.

Note

After you create an index, you need to select the index to use it for the table. For more information, see How to: Set Controlling Indexes.

To create an index using the Table Designer

  1. Open the Table Designer to modify your table.

  2. In the Table Designer, choose the Indexes tab.

  3. In the Name box, type a name for the index tag.

  4. In the Type list, choose the index type you want.

  5. In the Expression box, type the name of the field that you want use to organize the records.

    -OR-

    Build an index expression by clicking the ellipsis (...) button to the right of the box.

    Note

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

  6. Choose OK.

For more information about index types, see Visual FoxPro Index Types.

For considerations concerning index expressions, see Index Creation Based on Expressions and Considerations for Creating Index Expressions.

You can add a filter to the index to select only those records that match the filter expression you supply. For more information, see How to: Filter Data.

When you create an index for a table, Visual FoxPro automatically creates a structural compound index (.cdx) file to store the index. This structural .cdx file has the same name as the table and opens automatically when the table opens. For more information about index files, see Visual FoxPro Index Files.

Tip

By default, Visual FoxPro displays records in ascending order. You can specify to display records in descending order when you create the index or after you create the index. For more information, see How to: Display Records in Descending Order.

As you work with records in tables, you might need to access records in different orders. For example, you might want to organize a customer table by contact name to quickly find the name you want or by postal code to generate mailing labels that are presorted for efficient mailing. You can create and store different ordering scenarios for tables by creating multiple indexes for the same table.

Creating Indexes Programmatically

You can create indexes in structural compound index (.cdx) files using the Visual FoxPro language. You can create an index when you create a table using the SQL CREATE TABLE command or create an index for an existing table using the SQL ALTER TABLE command or the INDEX command.

Note

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

To create an index in a structural .cdx file when creating a table

  1. Use the SQL CREATE TABLE command to specify a name and fields for the table.

  2. To create a primary index, include the PRIMARY KEY clause for the corresponding field that you want to use.

    -OR-

    To create a candidate index, include the UNIQUE clause.

    Note

    Including the UNIQUE clause is not the same as specifying a unique index.

    -OR-

    For all other indexes, include the FOREIGN KEY clause.

To create an index in a structural .cdx file for an existing table

  1. Use the SQL ALTER TABLE command to specify a name and fields for the table.

  2. To create a primary index, include the PRIMARY KEY or ADD PRIMARY KEY clause for the corresponding field that you want to use.

    -OR-

    To create a candidate index, include the ADD UNIQUE clause.

    -OR-

    For all other indexes, include the ADD FOREIGN KEY clause.

For example, using the Customer table from the Visual FoxPro sample database, TestData, you can use either of the following commands to assign the Cust_ID field as the primary key for the Customer table:

ALTER TABLE Customer ADD PRIMARY KEY Cust_ID TAG Cust_ID
ALTER TABLE Customer ALTER COLUMN Cust_ID c(5) PRIMARY KEY

For more information, see CREATE TABLE - SQL Command, ALTER TABLE - SQL Command, and INDEX Command.

You can also create indexes in structural .cdx files by copying from one or more standalone index (.idx) files using the COPY INDEXES command and omitting the TO clause.

To create in index in a structural .cdx file from an .idx file

  1. Use the COPY INDEXES command to specify one or more names of .idx files. To include each index key from all open .idx files, use the ALL keyword.

  2. Omit the TO clause.

For more information, see COPY INDEXES Command.

See Also

Tasks

How to: Create Less Frequently Used Indexes

Other Resources

Working with Table Indexes