Home
SQL programmingYou can use the HCL OneDB™ implementation of the SQL language to develop applications for HCL OneDB™ database servers.
Guide to SQL: SyntaxThe HCL OneDB™ Guide to SQL: Syntax describes the syntax of the statements, data types, expressions, operators, and built-in functions of the HCL OneDB™ dialect of the SQL language.
SQL statementsThis chapter describes the syntax and semantics of SQL statements that are recognized by HCL OneDB™.
CREATE INDEX statementUse the CREATE INDEX statement to create an index for one or more columns in a table, or on values returned by a UDR that uses column values as arguments.
Index modesUse the index mode options of the CREATE INDEX statement to specify the behavior of the index during INSERT, DELETE, MERGE, and UPDATE operations.
Specifying Modes for Unique Indexes

Specifying Modes for Unique Indexes

You must observe the following guidelines when you specify modes for unique indexes in CREATE INDEX statements:

You can set the mode of a unique index to enabled, disabled, or filtering.
If you do not specify a mode, then by default the index is enabled.
For an index set to filtering mode, if you do not specify an error option, the default is WITHOUT ERROR.
When you add a new unique index to an existing base table and specify the disabled mode for the index, your CREATE INDEX statement succeeds even if duplicate values in the indexed column would cause a unique-index violation.
When you add a new unique index to an existing base table and specify the enabled or filtering mode for the index, your CREATE INDEX statement succeeds provided that no duplicate values exist in the indexed column that would cause a unique-index violation. However, if any duplicate values exist in the indexed column, your CREATE INDEX statement fails and returns an error.
When you add a new unique index to an existing base table in the enabled or filtering mode, and duplicate values exist in the indexed column, erroneous rows in the base table are not filtered to the violations table. Thus, you cannot use a violations table to detect the erroneous rows in the base table.