Oracle9i view constraints - NOT NULL, unique constraints, primary key, foreign key

Call (800) 766-1884 for Oracle support & training

Donald K. Burleson

Oracle Tips

New Oracle9i view constraints

Prior to Oracle9i, Oracle views were always dynamically generated from their base tables, and the view could not contain constraints.

As a quick refresher, Oracle views are used to simplify a complex query by hiding all of the internal table joins operations.

For example, here is a view of display order information for the Widget products:

create or replace view

widget_orders

select

cust_name,

order_date,

product_name,

sum(qty*price) total cost

from

customer

natural join

orders

natural join

order_item

natural join

product

where

product_type = ‘widget’;

Once this view is defined, we can issue complex queries against the view:

select * from widget_orders where order_date > sysdate-5;

The problem with traditional views is that referential integrity constraints cannot be defined against our view. Starting in Oracle9i, Oracle supports the following constraints on views:

NOT NULL – This is always inherited directed from the base tables that make-up the view.
Unique constraints – Oracle9i allows for unique constraints to be defined upon any column of the view.
Primary key – Today we can get primary key constraints defined directly upon the view.
Foreign key – Foreign key referential integrity is now directly available whenever a view has foreign key dependencies against other base tables.

As you may know, managing referential integrity within views can have a severe impact on query performance. Here is a great article on this subject called Guard against performance issues when using Oracle Views.

With Oracle9i, 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;