Re: Referece to View as foreign key

Alexander Portwein wrote:

> What I am trying to do is following:
>
> SQL> desc Artikel;
> Name Null? Typ
> ----------------------------------------- --------
> ----------------------------
> TNR NOT NULL NUMBER(38)
> BEZEICHNUNG NOT NULL VARCHAR2(50)
> ARTIKEL_TYP NOT NULL VARCHAR2(50)
> VERKAUFSPREIS NUMBER
> JAHRESUMSATZ NUMBER
> ZEITSTEMPEL DATE
>
> SQL>create view artikel_view as select * from artikel;
>
> SQL> create table test_view
> 2 (ID NUMBER(30) Constraint test_view_pk PRIMARY KEY,
> 3 TNR_ART NUMBER(30) NOT NULL,
> 4 CONSTRAINT test_view_fk FOREIGN KEY (TNR_ART) REFERENCES
> ARTIKEL_VIEW(TNR));
> CONSTRAINT test_view_fk FOREIGN KEY (TNR_ART) REFERENCES
> ARTIKEL_VIEW(TNR))
>
> That gives me Error: ORA-02270
>
> Why is this? Isnt it possible to delare the ID from a View as a primary
> key in Oracle?
>
> Your help will be greatly appreciated!

Not possible and not desirable.

A foreign key reference must be unique ... and that means enforced by a primary key or unique constraint.

There is no reason to even attempt creating a referential reference to a view when you can do it to the underlying table.

Daniel Morgan Received on Sat Mar 15 2003 - 17:56:06 CST