Referece to View as foreign key

[Alexander Portwein]

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!

[DA Morgan]

Alexander Portwein wrote:

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

[Alexander Portwein]

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

Exactly! I do not have acces to the underlying Table - nevertheless I
need some kind of reference...what do I do now?

Regards
Alex

[DA Morgan]

Alexander Portwein wrote:

Then one can draw one of only two conclusions.

1. No foreign key is possible because your DBA and management have decided
that you, and your work, do not warrant granting permission to create one.

2. You need to talk to your DBA and management, explain what you need, and ask
them to grant you the appropriate privilege.

There are no work-arounds to Oracle security. If there were ... there would be
no security.

Daniel Morgan

[Jusung Yang]

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.

- Jusung Yang

Alexander Portwein <m0shp...@gmx.de> wrote in message news:<b500t6$lbb$03$1...@news.t-online.com>...