Guarantee a Unique Index

A unique index ensures that no two rows have the same index value, including NULL. The system checks for duplicate values when the index is created, if data already exists, and checks each time data is added or modified with an insert or update.

You can easily enforce uniqueness—using the unique keyword—on global indexes because they are not partitioned. Local indexes are partitioned; enforcing uniqueness requires additional constraints.

To enforce uniqueness on local indexes, the partition keys must:

Be a subset of the index keys
Have the same sequence as the index keys

For example, you can impose uniqueness in these instances:

A table partitioned by hash, list, or range on column1, with a local index with index key on column1.
A table partitioned by hash, list, or range on column1, with a local index with index keys on column1 and column2.
A table is partitioned by hash, list, or range on column1 and column3. A local index has these index keys:
- column1, column3, or
- column1, column2, column3, or
- column0, column1, column3, column4.
An index with these index keys cannot enforce uniqueness: column3 or column1, column3.

You cannot enforce uniqueness on round-robin partitioned tables with local indexes.