New Oracle9i view constraints

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