Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Referece to View as foreign key

Re: Referece to View as foreign key

From: DA Morgan <damorgan_at_exxesolutions.com>
Date: Sat, 15 Mar 2003 15:56:06 -0800
Message-ID: <3E73BD96.FDB3A9B6@exxesolutions.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US