Oracle Database - Integrity Constraint
Data warehouse
Constraints can be used for these purposes in a data warehouse:
- Data Quality
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. They are the key part of the Oracle Database to ensure referential integrity.
- 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.
Articles Related
Overview of Constraint Clause
To understand how best to use constraints, you should first understand the basic purposes of constraints.
Enable
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 error.
Validate
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).
When you can create a no validate constraint, you ask the database to not validate the data already present in the table.
Rely
RELY (or 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. The RELY state provides you with a mechanism for telling Oracle that a given constraint is believed to be true.
Key
UNIQUE Key
A UNIQUE key integrity constraint requires 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.
FOREIGN KEY
A foreign key is a referential constraint between two tables. The foreign key identifies a column or a set of columns in one (referencing) table that refers to set of columns in another (referenced) table.
Parallelism
All constraints can be validated in parallel. When validating constraints on very large tables, parallelism is often necessary to meet performance goals. The degree of parallelism for a given constraint operation is determined by the default degree of parallelism of the underlying table.
Partitioning
You can create and maintain constraints before you partition the data.
View Constraints
You can create constraints on views. The only type of constraint supported on a view is a RELY constraint.
This type of constraint is useful when queries typically access views instead of base tables, and the database administrator thus needs to define the data relationships between views rather than tables. View constraints are particularly useful in OLAP environments, where they may enable more sophisticated rewrites for materialized views.
