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.