The Database Design Resource Center



Unique Constraints

UNIQUE constraints are 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.

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.
A unique index is required for unique constraints to ensure that each individual row modified in the 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_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


Exclusive interviews with:
Steven Feuerstein, PLSQL expert
Donald Burleson, Top IT consultant


Free eBook

Subscribe to my newsletter and get my ebook on Entity Relationship Modeling Principles as a free gift:


What visitors say...

"I just stumbled accross your site looking for some normalization theory and I have to say it is fantastic.

I have been in the database field for 10+ years and I have never before come across such a useful site. Thank you for taking the time to put this site together."

Mike, USA

Read more Testimonials



Database Normalization eBook:


Database Normalization eBook




Copyright © www.databasedesign-resource.com /
All rights reserved.
All information contained on this website is for informational purposes only.
Disclaimer: www.databasedesign-resource.com does not warrant any company, product, service or any content contained herein.

Return to top

Copyright acknowledgement note:

The name Oracle is a trademark of Oracle Corporation.
The names MS Access/MS SQL Server are trademarks of Microsoft Corporation.
Any other names used on this website may be trademarks of their respective owners, which I fully respect.