picoSQL - Language reference manual

Syntax:

CREATE [UNIQUE] INDEX index-name
ON table-name (column-name [ASC|DESC] [, column-name[ASC|DESC]] ...)

Purpose:

Allows the creation of an index on the specified table. Indexes are important to improve performances.

See also:

DROP

Description:

This statement creates an index on the specified columns of the specified table. Indexes are automatically used by picoSQL to improve performances in the searching and when an ordering is selected using the ORDER BY clause.

An index can be created on a table in any time, also if the table is not empty.

The UNIQUE clause ensures that there will not be two rows in the table with the same values in all the columns of the index.

The index-name is not used by picoSQL, but it must be specified for compatibility with SQL standard. Every index has a conventional name, formed in the following way:

table-name_progressive-number

The progressive number start from 02 because number 01 is reserved to primary key. The index conventional name must be used in the DROP INDEX statement to drop an existant index.

If you drop the nth index, the (n+1)th index becomes the nth and so on.

An index can be formed from 8 parts, and a part can be formed from more than one adjacent columns of non-native type (CHAR, VARCHAR, NUMERIC, DATE, TIMESTAMP). An index can be altogether 255 characters long.

The DESC clause is not implemented in the current release.