Unique key implementation in a relational environment
A unique constraint is a constraint that enforces 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.
A unique constraint is also known as a:
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.
A unique constraint allow the input of nulls unless you also define NOT NULL constraints for the same columns.
The unique key is not the access key as it can change over time. If you need to retrieve a record in a table, the primary key must be used.
A simple unique key is a unique key that consists of one column.
A composite unique key is a UNIQUE key that consists of group of columns (>1)
Database enforces generally the UNIQUE key constraint by implicitly creating a unique index on the composite unique key.
Because the creation and management of a index is time consuming, for performance reason, the creation of a unique index may be disabled. The unique constraint stays to give information to the query optimizer on the data but is not enforced.
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 (Oracle)
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:
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:
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