In oracle, you can create constraints on views in addition to
constraints on tables.
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
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
- 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
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
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)
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
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
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
- 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
- You cannot define view constraints on
attributes of an object column.
Return to Oracle Database constraints