Create and Drop Index Statements

The dBASE driver supports SQL statements to create and delete indexes.

The type of index you create is determined by the value of the CreateType attribute, which you set in the driver Setup dialog box (for UNIX and Linux, edit the system information file) or as a connection string attribute. The index can be:

■	dBASE IV or V (.MDX)

■	Clipper (.NTX)

■	FoxPro (.CDX)

The syntax for creating an index is:

CREATE [UNIQUE] INDEX index_name ON base_table_name
(field_name [ASC | DESC] [,field_name [ASC | DESC]]...)

Unique means that the driver creates an ANSI-style unique index over the column and ensures uniqueness of the keys. Use of unique indexes improves performance. ANSI-style unique indexes are different from dBASE-style unique indexes. With ANSI-style unique indexes, you receive an error message when you try to insert a duplicate value into an indexed field. With dBASE-style unique indexes, you do not see an error message when you insert a duplicate value into an indexed field. This is because only one key is inserted in the index file.

index_name is the name of the index file. For FoxPro and dBASE IV or V, this is a tag, which is required to identify the indexes in an index file. Each index for a table must have a unique name.

base_table_name is the name of the database file whose index is to be created. The .DBF extension is not required; the driver automatically adds it if it is not present. By default, dBASE IV or V index files are named base_table_name.MDX and FoxPro indexes are named base_table_name.CDX.

field_name is a name of a column in the dBASE table. You can substitute a valid dBASE-style index expression for the list of field names.

ASC tells dBASE to create the index in ascending order. DESC tells dBASE to create the index in descending order. By default, indexes are created in ascending order. You cannot specify both ASC and DESC orders within a single Create Index statement. For example, the following statement is invalid:

CREATE INDEX emp_i ON emp (last_name ASC, emp_id DESC)

Table 16-4 shows the attributes of the different index files supported by the dBASE driver. For each type supported, it provides the following details:

■	Whether dBASE-style unique indexes are supported

■	Whether descending order is supported

■	The maximum size supported for key columns

■	The maximum size supported for the column specification in the Create Index statement

■	Whether production/structural indexes are supported

Table 16-4. dBASE-Compatible Index Summary
Create Type .Extension	dBASE UNIQUE	DESC	Max Size of Key Column	Max Size of Column Specification	Production/ Structural Indexes	Supports FOR Expressions
dBASE IV, V .MDX	Yes	Yes	100	220	Yes	Yes
Clipper .NTX	Yes	Yes	250	255	No	Yes
FoxPro .IDX 1	Yes	Yes	240	255	No	Yes
FoxPro .CDX	Yes	Yes	240	255	Yes	Yes

Compact IDX indexes have the same internal structure as a tag in a CDX file. These indexes can be created if the IDX extension is included with the index name in the Create Index statement.

Drop Index

The syntax for dropping an index is as follows:

DROP INDEX table_name.index_name

table_name is the name of the dBASE file without the extension.

For FoxPro and dBASE IV or V, index_name is the tag. Otherwise, index_name is the name of the index file without the extension.

To drop the index EMPHIRE.MDX, issue the following statement:

DROP INDEX emp.emphire