Integrity Constraints in a Data Warehouse

Integrity constraints provide a mechanism for ensuring that data conforms to
guidelines specified by the database administrator. The most common types of
constraints include:
■ UNIQUE constraints
To ensure that a given column is unique
■ NOT NULL constraints
To ensure that no null values are allowed
■ FOREIGN KEY constraints
To ensure that two keys share a primary key to foreign key relationship
Constraints can be used for these purposes in a data warehouse:
■ Data cleanliness
Constraints verify that the data in the data warehouse conforms to a basic level of
data consistency and correctness, preventing the introduction of dirty data.
■ Query optimization
The Oracle Database utilizes constraints when optimizing SQL queries. Although
constraints can be useful in many aspects of query optimization, constraints are
particularly important for query rewrite of materialized views.
Unlike data in many relational database environments, data in a data warehouse is
typically added or modified under controlled circumstances during the extraction,
transformation, and loading (ETL) process. Multiple users normally do not update the
data warehouse directly, as they do in an OLTP system.

Posted in: Constraints