Re: Foreign key reference to a view column

Hi Gian,

Since the other tables that refered to the original customer table still have a foreign key reference on the customer number you do not need to simulate a foreign key referencing a view column. See an example below.

SQL> create table a(id number primary key);

Table created.

SQL> create table b(fk number referencing a);

Table created.

SQL> alter table a add(
  2 ver number);

Table altered.

SQL> create view a_view as select id,ver from a where ver=1;

View created.

SQL> insert into a(id,ver) values(100,1);

1 row created.

SQL> insert into b(fk) values(100);

1 row created.

SQL> insert into b(fk) values(101);
insert into b(fk) values(101)
*
ERROR at line 1:
ORA-02291: integrity constraint (OFEDOROV.SYS_C001070) violated - parent key not found

We was not able to inser a "wrong" record.

Good luck.

Oleg

"GFC" <gfc.news.invalid_at_web2news.net> wrote in message news:28745N450_at_web2news.com...
> Hello,
>
> Can someone help me with the following:
>
> I recently had to modify a customer table by adding a version column
> next to each customer number. This to allow tracking changes of customer
> information.
>
> Other tables refered to the original customer table with a foreign key
> reference on the customer number.
>
> Now I have made a view on the new customer table so that only the
> highest customer version is shown, thereby simulating the original
> functionality.
>
> The problem is that the original foreign key constraint cannot be used
> on the view so my application can't validate customer numbers anymore.
>
> How can I simulate a foreign key referencing a view column?
>
> Has anyone encountered a similar problem?
>
> Thanks,
>
> Gian Franco
> --
> Direct access to this group with http://web2news.com
> http://web2news.com/?comp.databases.oracle.misc
Received on Mon May 05 2003 - 13:54:15 CDT