Restrictions on columns as index keys

Whether the database server accepts the Index Key specification as valid can depend on various factors, including attributes of the table, and the data types, the storage size, and the total number of index-key columns, as well as dependencies between index-key columns and constraints on the same table.

The following restrictions apply to any column or column list that the Index Key Specification of the CREATE INDEX statement references:
  • All the columns must exist in the table on which the index is defined.
  • The table must exist in the current database, and cannot be an object that the CREATE EXTERNAL TABLE statement defined.
  • A column defined as an index key cannot be a complex data type (LIST, MULTISET, SET, a generic COLLECTION type, or a ROW type). However, you can create a functional index on a ROW type column.A column defined as an index key cannot be a complex data type (LIST, MULTISET, SET, a generic COLLECTION type, or a ROW type) or a JSON or BSON data type, with the following exceptions:
    • You can create a functional index on a column of a named ROW type.
    • You can create an index on a specific field of a BSON column, or create multiple indexes, each on a different field of the same BSON column. You cannot create an index on an entire BSON column.
  • The maximum number of columns and the total width of all column index keys are dependent on the page size of the database server. See Creating Composite Indexes.
  • You cannot add an ascending index to a column list on which a unique constraint is defined. See Using the ASC and DESC Sort-Order Options.
  • You cannot add a unique index to a column list that has a primary-key constraint. The reason is that defining the column or column list as the primary key causes the database server to implement the constraint by creating a unique internal index on the column or column list. The CREATE INDEX statement cannot define another unique index whose key is the same column or column list.
  • The number of indexes that you can define on the same list of columns is restricted. See Restrictions on the Number of Indexes on a Set of Columns.

For additional index-key restrictions that apply to columns that are specified as arguments to functional indexes, see Using the return value of a function as an index key.