The Database Design Resource Center

Oracle Constraints
on Views

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 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

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

    CREATE VIEW Emp_view
    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)

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

Exclusive interviews with:
Steven Feuerstein, PLSQL expert
Donald Burleson, Top IT consultant

Free eBook

Subscribe to my newsletter and get my ebook on Entity Relationship Modeling Principles as a free gift:

What visitors say...

"I just stumbled accross your site looking for some normalization theory and I have to say it is fantastic.

I have been in the database field for 10+ years and I have never before come across such a useful site. Thank you for taking the time to put this site together."

Mike, USA

Read more Testimonials

Database Normalization eBook:

Database Normalization eBook

Copyright © / Alf A. Pedersen
All rights reserved.
All information contained on this website is for informational purposes only.
Disclaimer: does not warrant any company, product, service or any content contained herein.

Return to top

Copyright acknowledgement note:

The name Oracle is a trademark of Oracle Corporation.
The names MS Access/MS SQL Server are trademarks of Microsoft Corporation.
Any other names used on this website may be trademarks of their respective owners, which I fully respect.