Re: Modifiable join view

Steve,

Two things:

1. The colc_1 is not in your view definition so even if the view were key preserved, it probably won't work
2. For key preservation, you need to define primary keys on your tables. Unique indexes are similar, but cannot take the place of primary key constraints, especially in this case. In this case, assuming the unique constraints are converted to primary keys, table test1 will not be key-preserved, but table test2 will. This is because there exists a many to one relationship between test1 and test2, so many rows of test2 can appear for each row of test1. So, although all of test1's PK fields appear in the view, it is not key preserved because a single row of the view does not map to a single row of the base table.

I think this is the way things work in Oracle. In any case, in this specific case, converting the unique indexes to primary keys will let you update test2 but not test1.

Hope this helps,

-Tom

"Steve Salvemini" <steve.salvemini_at_adelaide.edu.au> wrote in message news:3BD7D0A7.9E1F1EC6_at_adelaide.edu.au...
> Hi all, just a little problem I'm having but can't see an easy solution.
> Goes something like this, hopefully this is not too abstract:
>
> table test1 ->
> cola
> colb
> colc_1
>
> unique index on cola and colb
>
>
> table test2 ->
> cola
> colb
> colc_2
> cold_2
>
> unique index on cola, colb, colc_2
>
>
> create view view1 (cola, colb, cold_2)
> as select a.cola, a.colb, b.cold_2
> from test1 a, test2 b
> where a.cola = b.cola
> and a.colb = b.colb
> and colc_2 = 0
>
>
> my update then is
>
> update view1 set colc_1 = 'X'
> where colb = 'ACB'
> and colb = 'DEF'
>
> the error then is:
> ORA-01779: cannot modify a column which maps to a non key-preserved
> table
>
> The web page:
> http://www-wnt.gsi.de/oragsidoc/doc_804/appdev.804/a58241/ch4.htm#978
> seems pretty descriptive but I'm just not getting it.
>
> Can anyone see whats required to make this work?
>
> Thanks greatly for any help on this!
Received on Thu Oct 25 2001 - 12:47:14 CDT