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
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.
Many significant constraint features have been introduced for data warehousing. Readers familiar with Oracle's constraint functionality in Oracle database version 7 and Oracle database version 8.x should take special note of the functionality described in this chapter. In fact, many Oracle database version7-based and Oracle database version8-based data warehouses lacked constraints because of concerns about constraint performance. Newer constraint functionality addresses these concerns.