Re: Modifiable join view

Thanks Tom, I ran the example below, seems a unique index if enough for the update on the child (many) table. So the rule in this case is simply that you cannot update the master table if it is joined to a child table in a view, you can only update the child table.

Thats consistant with what I see below. Thanks again.

this example is a bit better:

create table test1
(cola varchar2(5),
colb varchar2(5),
colc_1 integer)

create unique index test1_i on test1(cola,colb)

create table test2
(cola varchar2(5),
colb varchar2(5),
colc_2 integer,
cold_2 integer)

create unique index test2_i on test2 (cola, colb, colc_2)

create view view1 (cola, colb, colc_1, cold_2) as select a.cola, a.colb, a.colc_1, b.cold_2 from test1 a, test2 b

where a.cola = b.cola
and   a.colb = b.colb
and   colc_2 = 0

update view1
--set colc_1 = 5 -- not ok

set cold_2 = 7    --  ok
where colb = 'ACB'
and   colb = 'DEF'

Tom Renfro wrote:
>
> 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!

-- 
-----------------------------------------------------------

Steven Salvemini
Peoplesoft Technical Specialist, Information Technology Services
ADELAIDE UNIVERSITY SA 5005
AUSTRALIA
Tel: +61 8 8303 6358  Fax: +61 8 8303 4400
Email: steve.salvemini_at_adelaide.edu.au

----------------------------------------------------------- 
This email message is intended only for the addressee(s) 
and contains information which may be confidential and/or
copyright.  If you are not the intended recipient please
do not read, save, forward, disclose, or copy the contents
of this email. If this email has been sent to you in error,
please delete this email and any copies or links to this
email completely and immediately from your system.  No 
representation is made that this email is free of viruses.
Virus scanning is recommended and is the responsibility of 
the recipient.