Unique and Non-Unique Index

Unique index

• Unique indexes enforce the uniqueness of a single column or a group of columns.
• They help maintain data integrity by ensuring that no two rows of a table have identical key values.
• When a unique index is defined for a table, uniqueness is enforced whenever keys are added or changed within the index - that is, on every INSERT and UPDATE.
• DB2 automatically creates a unique index behind the scenes when you define a PRIMARY KEY or a UNIQUE constraint.

Non-unique index

• Non-unique indexes are not used to enforce constraints on the tables with which they are associated.
• They are used solely to improve query performance by maintaining a sorted order of data values that are used frequently in WHERE clauses.
• Duplicate key values are fully allowed in a non-unique index.
• Most indexes in a busy OLTP system are non-unique, because only a few columns truly need uniqueness.

Example

• Unique index on one column:-
  
  CREATE UNIQUE INDEX UX_EMP_EMAIL ON EMP (EMAIL);
• Non-unique index on a group of columns:-
  
  CREATE INDEX IX_EMP_DEPT_JOB ON EMP (DEPTNO, JOB);