To define a view constraint in oracle9i

Before oracle9i, Oracle views are always generated dynamically from their base tables (base tables), and the view cannot contain constraints. The Oracle view simplifies complex queries by hiding all the internal table join operations.

For example, the following view displays ordering information for hardware (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 ';

After we have defined this view, we can make a complex query about it.

SELECT * from Widget_orders where order_date > sysdate-5;

The problem with traditional views is that we cannot define referential integrity constraints (referential integrity constraints) on the view. Starting with Oracle9i, Oracle supports the following view constraints.

Non-null (NOT NULL): This constraint is always inherited from the base table in which the view was created.
Uniqueness constraint (UNIQUE constraints): Oracle9i allows you to define a uniqueness constraint on any column of the view.
Primary KEY (Primary key): We can define primary key constraints directly to the view.
Foreign key (Foreign key): As long as the view has foreign keys that depend on other base tables, foreign key referential integrity is directly present.
As you know, managing a reference complete constraint on a view can greatly affect the performance of a query.

In oracle9i, we can avoid the problems caused by the unconstrained view. The following example creates a PRIMARY key constraint on a view.

Alter view
Widget_orders
Add constraint
Widget_orders_pk
Primary key
(Cust_name, Order_date, Product_Name)
Disable novalidate;