Interview Questions

Integrity Constraints

This chapter describes integrity constraints, and discusses:

  • Why Integrity Constraints are Useful in a Data Warehouse
  • Overview of Constraint States
  • Typical Data Warehouse Integrity Constraints

Why Integrity Constraints are Useful 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.

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.

Pragna Meter
Next Chapter  
e-University Search
Related Jobs