SQL/MX 2.x Reference Manual (H06.04+)

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—540440-003
2-53
Syntax Description of CREATE INDEX
Syntax Description of CREATE INDEX
UNIQUE
specifies that the values (including NULL values) in the column or set of columns
that make up the index field cannot contain more than one occurrence of the same
value or set of values. For indexes with multiple columns, the values of the
columns as a group determines uniqueness, not the values of the individual
columns. If you omit UNIQUE, duplicate values are allowed. The columns you
specify for the index need not be declared NOT NULL (note that this is unlike
CREATE TABLE and ALTER TABLE, which do require all columns of a specified
unique constraint to be NOT NULL).
index
is an SQL identifier that specifies the simple name for the new index. You cannot
qualify index with its catalog and schema names. Indexes have their own
namespace within a schema, so an index name might be the same as a table or
constraint name. However, no two indexes in a schema can have the same name.
table
is the name of the table for which to create the index. See Database Object Names
on page 6-12.
column-name [ASC[ENDING] | DESC[ENDING]]
[,column-name [ASC[ENDING] | DESC[ENDING]]]...
specifies the columns in table to include in the index. The order of the columns in
the index need not correspond to the order of the columns in the table.
ASCENDING or DESCENDING specifies the storage and retrieval order for rows
in the index. The default is ASCENDING.
Rows are ordered by values in the first column specified for the index. If multiple
index rows share the same value for the first column, the values in the second
column are used to order the rows, and so forth. If duplicate index rows occur in a
nonunique index, their order is based on the sequence specified for the columns of
the key of the underlying table. For ordering (but not for other purposes), nulls are
greater than other values.
populate-option
NO POPULATE
specifies that the index is not to be populated when it is created. The index's
partition(s) are created, but no data is written to the index, and it is marked
“offline”. You can drop an offline index with the DROP INDEX statement. The
DROP TABLE statement also drops offline indexes of the specified table. DML
statements have no effect on offline indexes. If an index is created with the
intention of using it for a constraint, it must be populated before creating the