5.3.5 Points to consider in determining the columns to be defined for a unique index
A unique index is a B-tree index in which no column on which the B-tree index is defined contains any duplicated key values. This restriction on key value duplication is called the uniqueness constraint.
When you need to ensure that a column on which a B-tree index is defined will not have any duplicated key values, consider defining a unique index.
- Note
-
A unique index is also automatically defined when a primary key (uniqueness constraint definition) is defined during base table definition. For details about primary keys, see 5.2.9 Specifying a primary key (uniqueness constraint definition) (PRIMARY KEY) [Single-chunk table].
In the following cases, key values are not considered to be duplicated even if there are duplicate key values (the uniqueness constraint is not violated in these cases):
-
The duplicate key value is the null value.
-
In a multiple-column index, at least one of the columns has a different value.
You define an index to be a unique index by specifying the UNIQUE operand in the CREATE INDEX statement. When you specify the UNIQUE operand, you cannot perform a data addition or data updating operation that will result in a duplicated key value. Also, if you attempt to add a row that contains a particular key value, you will not be allowed to do so if another executing transaction has already added a row with that same key value.
- Important
-
Even when a unique index is defined, the uniqueness constraint cannot be guaranteed in the following cases (key value duplication might occur):
-
Data is imported by the adbimport command.
-
A B-tree index is rebuilt by the adbidxrebuild command.
-