Unique Constraints

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.

a unique constraint doesn't allow duplicate values in a column.

If a constraint encompasses two or more columns, no two equal combinations are allowed.

A unique constraint is a single field or combination of fields that uniquely defines a record. Some of the fields can contain null values as long as the combination of values is unique.

In Oracle, a unique constraint can not contain more than 32 columns.

Unique constraints can be defined in either a CREATE TABLE statement or an ALTER TABLE statement.

These are the ways to create the constraint :

CREATE TABLE table_name
(column1 datatype null/not null,
column2 datatype null/not null,
...

CONSTRAINT constraint_name UNIQUE (column1, column2, . column_n));

ALTER TABLE sales ADD CONSTRAINT sales_unique
UNIQUE(sales_id);

Here the column sales.sales_id uniquely identifies a single sales transaction, and the data warehouse administrator must ensure that this column is unique within the data warehouse.

Although the behavior of primary key and unique constraints is the same still there is a basic difference between them.

None of the fields that are part of the primary key can contain a null value but some of the fields that are part of the unique constraint can contain null values as long as the combination of values is unique.

Moreover, Oracle does not permit you to create both a primary key and unique constraint with the same columns. By default, unique constraints are both enabled and validated.

Oracle implicitly creates a unique index on the corresponding attribute 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 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 a table is partitioned along a column other than the unique attribute then the unique index must be global. This can surely affect all maintenance operations on the table.

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

ALTER TABLE sales ADD CONSTRAINT sales_unique
UNIQUE (sales_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:

Drop a Unique Constraint

The syntax for dropping a unique constraint is:

ALTER TABLE table_name
drop CONSTRAINT constraint_name;

Disable a Unique Constraint

The syntax for disabling a unique constraint is:

ALTER TABLE table_name
disable CONSTRAINT constraint_name;

Enable a Unique Constraint

The syntax for enabling a unique constraint is:

ALTER TABLE table_name
enable CONSTRAINT constraint_name;

Return to Oracle Database constraints