Oracle Constraints
on Views

You can specify constraints on views and object views. You define the constraint at the view level using the out_of_line_constraint clause and constraint as part of column or attribute specification using the inline_constraint clause after the appropriate alias.

Here are some of the constraints, which can be applied to views in oracle:

1. View check option constraints
2. View read-only constraints
3. View primary key constraints
4. View unique key constraints
5. View RI constraints

The first two types of constraints on views have been around for several years but the last three have appeared more recently and exist to help the optimizer do clever tricks, particularly in the area of query rewrite.

Oracle views are used to simplify a complex query by hiding the entire internal table joins operations. Declarative primary key, unique key and foreign key constraints can now be defined against views as given below.

The constraints are not validated so they must be defined with the DISABLE NOVALIDATE clause. The problem with traditional views is that referential integrity constraints cannot be defined against any view.

Oracle supports the following constraints on views:

• NOT NULL: This is always inherited directly from the base tables that make-up the view so it cannot be declared explicitly.
• Unique constraints: Oracle allows for unique constraints to be defined upon any column of the view.

  ALTER VIEW Emp_view
  ADD CONSTRAINT emp_view_unq
  UNIQUE (first_name) DISABLE NOVALIDATE;

• Primary key: Today we can get primary key constraints defined directly upon the view.

  CREATE VIEW Emp_view
  (id PRIMARY KEY DISABLE NOVALIDATE, firstname)
  AS SELECT employee_id, first_name
  FROM employees
  WHERE department_id = 10;

• Foreign key: Foreign key referential integrity is now directly available whenever a view has foreign key dependencies against other base tables.

Managing referential integrity within views can have a severe impact on query performance.

With Oracle, we can bypass the traditional problems associated with non-constrained views. Here is an example of a primary key constraint created on a view:

ALTER VIEW widget_orders
ADD CONSTRAINT widget_orders_pk
PRIMARY KEY (cust_name, order_date, product_name)
DISABLE NOVALIDATE;

Another 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 DBA 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.

Constraints on Materialized views:

Oracle does not enforce view constraints.

However, operations on views are subject to the integrity constraints defined on the underlying base tables. This means that you can enforce constraints on views through constraints on base tables.

If the materialized view is being created with ON COMMIT REFRESH specified, then the owner of the materialized view requires an additional privilege if any of the tables in the defining query are outside the owner's schema.

In that case, the owner requires the ON COMMIT REFRESH system privilege or the ON COMMIT REFRESH object privilege on each table outside the owner's schema.

Restrictions on view constraints: View constraints are a subset of table constraints and are subject to the following restrictions:

• You can specify only unique, primary key, and foreign key constraints on views. However, you can define the view using the WITH CHECK OPTION clause, which is equivalent to specifying a check constraint for the view.
• Because view constraints are not enforced directly, you cannot specify INITIALLY DEFERRED or DEFERRABLE.
• View constraints are supported only in DISABLE NOVALIDATE mode. You must specify the keywords DISABLE NOVALIDATE when you declare the view constraint, and you cannot specify any other mode.
• You cannot specify the using_index_clause, the exceptions_clause clause, or the ON DELETE clause of the references_clause.
• You cannot define view constraints on attributes of an object column.

Return to Oracle Database constraints