Apollo SQL > DDL & DML Statements

The CREATE INDEX statement is used to create new index tag in a structural index (.CDX/.NSX) for a given table on the server. This command can also be used to create .NTX indexes for Clipper tables (ttSXNTX). Index names may not have embedded spaces.

$image\tip.gif$ Use ExecSQL from within in each Apollo interface to execute this statement.

Syntax:

CREATE [UNIQUE] [ASC | DESC] INDEX index_reference ON table_reference

(column_reference [,column_reference...])

Use UNIQUE to create an index that raises an error if rows with duplicate column values are inserted. By default, indexes are not unique.

Use ASC (or ASCENDING) to create an index that orders data in an ascending direction (smallest to largest). DESC (or DESCENDING) creates a descending ordering (largest to smallest). When a direction imperative is not specified, ASC is the implied default.

Examples:

/* For a FoxPro table, creates NAMES tag inside

CUSTOMER.CDX on "LAST+FIRST" */

CREATE INDEX names ON MyTable (last, first)

/* For a HiPer-SIx table, creates UNIQUE CUSTNO tag inside ORDERS.NSX on "CUSTNO" */

CREATE UNIQUE INDEX custno ON MyOrders (custno)

/* For a Clipper table, creates NAMES.NTX on "LAST+FIRST" */

CREATE INDEX names ON MyTable (last, first)