This command creates a new named index on a field or group of fields of some existing table. The same task can be done with the help of ALTER TABLE command. Notice that PRIMARY KEY can be created only with ALTER TABLE.
create_index : CREATE [UNIQUE] INDEX index_name ON table_name index_column_list index_column_list : ( column_name [ (UINT) ] [, column_name [ (UINT) ] ] )
Normally, you create all indexes using CREATE TABLE. In most cases you can use flag INDEXED to specify that a field should have index. Then index will get auto-generated name, which you can obtain using SHOW INDEXES command.
CREATE TABLE tblPerson( fldName VARCHAR(2044) INDEXED );
Sometimes you may need specify UNIQUE constraint on few fields, what force DB create compound index for that fields. The following example will create 3 indexes.
CREATE TABLE tblPerson ( fldFirstName VARCHAR(2044) INDEXED, fldLastName VARCHAR(2044) INDEXED, UNIQUE( fldFirstName, fldLastName ) );
The same can be done with two commands. The second command may happens much later, when table exists and you realize the need in one more index. In this case you need specify custom name for unique index.
CREATE TABLE tblPerson ( fldFirstName VARCHAR(2044) INDEXED, fldLastName VARCHAR(2044) INDEXED, ); CREATE UNIQUE INDEX ind_tbpPerson_U_FullName ON tblPErson( fldFirstName, fldLastName );
- if column list contains only one column, then such index in Valentina DB is considered to be on field-level, what you can see in the SHOW INDEXES command. This index also set ON flag fIndexed of the field. For Valentina DB absolutely not matter in which was you will create this one-field index, using flag or using CREATE INDEX command. You can remove this index also in two way: set flag OFF or using DROP INDEX command.
- A column list of the form (col1,col2,…) creates a multiple-column index. Index values are formed by concatenating the values of the given columns. Such index in Valentina DB considered to be on table-level.
CREATE INDEX T1_f1 ON T1( f1 );
CREATE INDEX T1_f3_f2 ON T1( f3, f2 );
CREATE UNIQUE INDEX T1_U_f3_f2 ON T1( f3, f2 );