602SQL Documentation Index  
<­Using Indexes not Containing NULL Values UpDatabase Table Indexes DROP INDEX Statement­>

CREATE INDEX Statement 

statement_CREATE_INDEX ::= CREATE [ IF NOT EXISTS ] [ UNIQUE ] INDEX index_name 
  ON [scheme.]table_name ( expression [ ordering ] {, expression [ ordering ] }… ) [{ NULL | NOT NULL }]
ordering ::= ASC | DESC

Description

The CREATE INDEX statement creates a new index for the specified table. It can be replaced by the more general statement ALTER TABLE. A default commit is executed after the successful creation of an index. The change cannot be rolled back in a transaction.

You cannot use this statement on a temporary table.

The reserved word UNIQUE causes a unique index to be created. A primary key cannot be created with this statement (use ALTER TABLE).

Index_name must be unique in the table context, otherwise, the error sqlstate W1095 (compiler error) occurs.

The index is described by a set of expressions that follow the reserved word ON. Each expression can be followed by the DESC word for descending sort. Ascending sort is used by default (ASC).

You can specify in the index definition whether to involve table rows with the NULL key value in the index. If the keyword NULL is specified in the statement, then the index contains keys with NULL values, if NOT NULL is specified, then rows with NULL key values are not in the index. If neither NULL or NOT NULL is specified the following is default: NONUNIQUE index may contain NULL values, UNIQUE index may not contain NULL values.

The interactive table designer is generally used for index creation.

602SQL Variations


Example:

CREATE UNIQUE INDEX ind ON Per_agen.Employed (number);
CREATE INDEX money ON Employed (salary DESC, surname)
<­Using Indexes not Containing NULL Values UpDatabase Table Indexes DROP INDEX Statement­>