Informatica Tutorials

Big Data Analytics

Overview of Integrity Constraint States

To understand how best to use constraints in a data warehouse, you should first
understand the basic purposes of constraints. Some of these purposes are:

■ Enforcement
In order to use a constraint for enforcement, the constraint must be in the ENABLE
state. An enabled constraint ensures that all data modifications upon a given table
(or tables) satisfy the conditions of the constraints. Data modification operations
which produce data that violates the constraint fail with a constraint violation

■ Validation
To use a constraint for validation, the constraint must be in the VALIDATE state. If
the constraint is validated, then all data that currently resides in the table satisfies the constraint.
Note that validation is independent of enforcement. Although the typical
constraint in an operational system is both enabled and validated, any constraint
could be validated but not enabled or vice versa (enabled but not validated). These
latter two cases are useful for data warehouses.

■ Belief
In some cases, you will know that the conditions for a given constraint are true, so
you do not need to validate or enforce the constraint. However, you may wish for
the constraint to be present anyway to improve query optimization and
performance. When you use a constraint in this way, it is called a belief or RELY
constraint, and the constraint must be in the RELY state. The RELY state provides
you with a mechanism for telling Oracle that a given constraint is believed to be
Note that the RELY state only affects constraints that have not been validated.

Related Posts Plugin for WordPress, Blogger...

Please Share

Twitter Delicious Facebook Digg Stumbleupon Favorites More

Follow TutorialBlogs
Share on Facebook
Tweet this Blog
Add Blog to Technorati