Re: create materialized view

fmarchioni_at_libero.it wrote:
> Hi all Oracle users !
> I'm trying to build a materialized view in
> order to import daily data from another DB
>
> CREATE MATERIALIZED VIEW VW_LINKS_155
> BUILD IMMEDIATE
> REFRESH FAST NEXT sysdate + 1
> AS SELECT * FROM VW_LINKS_155_at_snap_dblink
>
> The matter is that Oracle complains that VW_LINKS_155_at_snap_dblink (a
> remote view accessed via snap_dblink ) doesn't have a PK constraint.
>
> Forgive my ignorance but views cannot inherit PK from their tables nor
> it's possible to target a PK on a view ?
>
> Can you give me a good advice ?
> Thanks a lot
> Francesco
>

What version?
 From Oracle 9i New features
(http://www.oracle-base.com/articles/9i/SQLNewFeatures9i.php#ViewConstraints)

"
...
View Constraints
Declarative primary key, unique key and foreign key constraints can now be defined against views. The NOT NULL constraint is inherited from the base table so it cannot be declared explicitly. The constraints are not validated so they must be defined with the DISABLE NOVALIDATE clause:

CREATE VIEW Emp_view
  (id PRIMARY KEY DISABLE NOVALIDATE, firstname) AS SELECT employee_id, first_name
FROM employees
WHERE department_id = 10;

ALTER VIEW Emp_view
ADD CONSTRAINT emp_view_unq
UNIQUE (first_name) DISABLE NOVALIDATE;
...
"

or

"
CREATE OR REPLACE VIEW <view_name>
(<column_name, column_name>
UNIQUE RELY DISABLE NOVALIDATE,
CONSTRAINT <constraint_name>
PRIMARY KEY (<column_name>) RELY DISABLE NOVALIDATE) AS <select statement>;

CREATE OR REPLACE VIEW person_pk_view
(person_id, last_name
UNIQUE RELY DISABLE NOVALIDATE,
CONSTRAINT pk_person_view
PRIMARY KEY (person_id) RELY DISABLE NOVALIDATE) AS SELECT person_id, last_name FROM person;

SELECT constraint_name, constraint_type
FROM user_constraints
WHERE table_name = 'PERSON_PK_VIEW';
" Received on Thu Feb 10 2005 - 12:34:33 CST