Tutorials to .com

Tutorials to .com » Database » Oracle » Oracle9i defined in the view of constraints

Oracle9i defined in the view of constraints

Print View , by: iSee ,Total views: 14 ,Word Count: 536 ,Date: Mon, 24 Aug 2009 Time: 3:59 PM

In the Oracle9i before the appearance, oracle views are always from their base form (base tables) in the dynamically generated, and the view can not contain constraints. By hiding all internal tables join operations, Oracle views can simplify complex queries.

For example, the following view to show hardware (widget) products, ordering information.

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

In the definition of this view, we will be able to carry out complex queries over it.

select * from widget_orders where order_date> sysdate-5;

The traditional view of the problems arising from the view that we can not define referential integrity constraints (referential integrity constraints). From the Oracle9i started, Oracle supports the following view of constraints.

Non-null (NOT NULL): This constraint is always to create the view from the base table inherited.

The only constraint (Unique constraints): Oracle9i allows view of arbitrary definition of a unique constraint column.

The primary key (Primary key): we can view definition directly to the primary key constraint.

Foreign key (Foreign Key): as long as the view that depend on other base table foreign key, then there will be a direct foreign key referential integrity.

As you know, as a reference for a complete view of constraint management will greatly influence the query performance.

In Oracle9i, we can avoid non-binding view of the problems brought about. The following example creates a view of the primary key constraint.

alter view

widget_orders

add constraint

widget_orders_pk

primary key

(cust_name, order_date, product_name)

disable novalidate;

Note: The author Donald Bunleson is a 23-year experience with database administrator, who prepared the 14 databases of books and published more than 100 articles. He is also editor in chief of Oracle Internals, and he also opened Bunleson Oracle consulting firm.

(Editor: Ming Ming mingming_ky@126.com TEL: (010) 68476606)




Oracle Tutorial Articles


Can't Find What You're Looking For?


Rating: Not yet rated

Comments

No comments posted.