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: Jusung Yang <JusungYang_at_yahoo.com>
Date: 18 Mar 2003 10:53:19 -0800
Message-ID: <130ba93a.0303181053.18eb294b@posting.google.com>


Technically, you can do it. But, as Daniel already pointed out, you need to resolve the underlying user sesurity issue with your DBA and management. Also, the end results may not be what you are looking for. You will not be able to use the it for data validation.

You can create foreign keys that reference a view which has primary key constraint (not sure since when this became possible). But the PK constraint on the view must be in "disable, novalidate" state. Consequently, the FK will also need to be created in "disable, novalidate" state. Here is an examle:

SQL> create or replace view t1_vw(c1, constraint c1_pk primary key (c1) disable novalidate) as select * from t1;

View created.

SQL> create table t2 (c1, constraint c1_fk foreign key (c1) references t1_vw(c1));
create table t2 (c1, constraint c1_fk foreign key (c1) references t1_vw(c1))
*
ERROR at line 1:
ORA-02270: no matching unique or primary key for this column-list

SQL> create table t2 (c1, constraint c1_fk foreign key (c1) references t1_vw(c1) disable novalidate)
;

Table created.

Since both PK and FK are disable, you can not really use them for data validation. Any attempt to enable the PK on the view will result in errors:

SQL> alter view t1_vw enable constraint c1_pk; alter view t1_vw enable constraint c1_pk

                 *

ERROR at line 1:
ORA-00922: missing or invalid option

SQL> alter view t1_vw modify constraint c1_pk rely;

View altered.

SQL> If one can not use these PK, FK for data validation, why would one create them? Well, it populates the data dictionary and gives you a picture of how the entities are realted to one another. It also has implications on "query rewrite". This is most useful in a data warehouse environment. It is pretty involved. Look up ORALCE DOC for more details.

Alexander Portwein <m0shpitone_at_gmx.de> wrote in message news:<b500t6$lbb$03$1_at_news.t-online.com>...
> 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!
Received on Tue Mar 18 2003 - 12:53:19 CST

Original text of this message

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