1 - Oracle Database - UNIQUE Constraints

2 - About

A UNIQUE key integrity constraint enforce the Entity Integrity e.g. that every value in a column or set of columns (key) be unique—that is, no two rows of a table have duplicate values in a specified column or set of columns.

UNIQUE key constraints allow the input of nulls unless you also define NOT NULL constraints for the same columns.

Name Definition
unique key The columns included in the definition of the UNIQUE key constraint
composite unique key UNIQUE key consists of group of columns (>1)

Unique key is often incorrectly used as a synonym for the terms UNIQUE key constraint or UNIQUE index. However, note that key refers only to the column or set of columns used in the definition of the integrity constraint.

Oracle enforces the UNIQUE key constraint by implicitly creating a unique index on the composite unique key. Therefore, composite UNIQUE key constraints have the same limitations imposed on composite indexes: up to 32 columns can constitute a composite unique key.

Advertising

4 - Unique constraint on a large table

4.1 - The problematic

A UNIQUE constraint is typically enforced using a UNIQUE index. However, in a data warehouse whose tables can be extremely large, creating a unique index can be costly both in processing time and in disk space.

Suppose that a data warehouse contains a table sales, which includes a column sales_id. sales_id uniquely identifies a single sales transaction, and the data warehouse administrator must ensure that this column is unique within the data warehouse.

One way to create the constraint is as follows:

ALTER TABLE sales ADD CONSTRAINT sales_uk
UNIQUE (prod_id, cust_id, promo_id, channel_id, time_id);

By default, the unique constraint is both enabled and validated. Oracle implicitly creates a unique index on sales_id to support this constraint.

However, this index can be problematic in a data warehouse for three reasons:

  • The unique index can be very large, because the sales table can easily have millions or even billions of rows.
  • The unique index is rarely used for query execution. Most data warehousing queries do not have predicates on unique keys, so creating this index will probably not improve performance.
  • If sales is partitioned along a column other than sales_id, the unique index must be global. This can detrimentally affect all maintenance operations on the sales table.

4.2 - An alternative mechanism - DISABLE VALIDATE

A unique index is required for unique constraints to ensure that each individual row MODIFIED in the sales table satisfies the unique constraint.

For data warehousing tables, an alternative mechanism for unique constraints is illustrated in the following statement:

ALTER TABLE sales ADD CONSTRAINT sales_uk
UNIQUE (prod_id, cust_id, promo_id, channel_id, time_id) DISABLE VALIDATE;

This statement creates a unique constraint, but, because the constraint is disabled, a unique index is not required. This approach can be advantageous for many data warehousing environments because the constraint now ensures uniqueness without the cost of a unique index.

However, there are trade-offs for the data warehouse administrator to consider with DISABLE VALIDATE constraints. Because this constraint is disabled, no DML statements that modify the unique column are permitted against the sales table. You can use one of two strategies for modifying this table in the presence of a constraint:

  • Use DDL to add data to this table (such as exchanging partitions).
  • Before modifying this table, drop the constraint. Then, make all necessary data modifications. Finally, re-create the disabled constraint. Re-creating the constraint is more efficient than re-creating an enabled constraint. However, this approach does not guarantee that data added to the sales table while the constraint has been dropped is unique.
Advertising

5 - Example and Performance

With unique constraint :

SQL> SET timing ON;
SQL> INSERT INTO d_material_2 SELECT * FROM d_material;
 
99384 rows created.
 
Elapsed: 00:00:00.31

Without unique constraint:

SQL> INSERT INTO d_material_2 SELECT * FROM d_material;
 
99384 rows created.
 
Elapsed: 00:00:00.71

With unique constraint RELY DISABLE NOVALIDATE

SQL> ALTER TABLE QS_DWH.D_MATERIAL_2 ADD CONSTRAINT D_MATERIAL_2_U01 UNIQUE (DIMENSION_KEY)
  2  RELY DISABLE NOVALIDATE;
 
TABLE altered.
 
Elapsed: 00:00:00.01
SQL> INSERT INTO d_material_2 SELECT * FROM d_material;
 
99384 rows created.
 
Elapsed: 00:00:00.31