SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
C-143
CREATE INDEX Statement
UNIQUE
specifies that values in the column or set of columns that make up the index field
cannot be the same for two or more rows of the table. For indexes with multiple
columns, the value of the columns as a group determines uniqueness, not the
values of the individual columns.
SQL cannot create a UNIQUE index if any col specified for the index is a column
that allows null values or if the underlying table has duplicate row values for the
group of indexed columns.
index
is a Guardian name (or an equivalent DEFINE) for the new index. The fully
expanded index name must be unique in the network. If the index is partitioned,
index identifies the primary partition.
If SMF is installed on your node, the volume portion of index can be either a
direct or virtual volume. If you specify only a subvolume and index name, SQL
creates an index in the current default volume. If the default volume is virtual, the
index resides on the virtual volume. If the default volume is direct, the index
resides on the physical volume as a direct file not managed by SMF.
index can reside on any node or volume, independent of the location of the
underlying table, but the volume on which the index is created must be audited by
the TMF subsystem, even if the index itself is nonaudited. (An index is nonaudited
if its underlying table is nonaudited.)
table
is the name of the table for which to create the index (or an equivalent DEFINE).
col [ ASC[ENDING] | DESC[ENDING] ] [ collate-spec ]
specifies a column to include in the index, the order in which to store and retrieve
key values in the column within the index, and a collating sequence for the column
within the index.
The number of columns allowed in an index depends on the length of the index
key. For more information on limitations, see Index Keys on page I-9.
col must be a column in table, but does not need to be adjacent to other
columns specified for the index or in the same order relative to other columns as in
the table.
ASCENDING is the default order for col.