Key-preserved table concept in join view (Ask Tom)

You Asked

Could you explain me about  Key-preserved table  concept
in join views.i know that a table is ket preserved if every key of the table can also be 
a key of result of the join.
but i not understand meaning of this.

Thanks 

and we said...

Key preserved means the row from the base table will appear AT MOST ONCE in the output 
view on that table.

For example, lets say I have tables T1 and T2 and a view:

create view T
as
select * from t1, t2 where t1.x = t2.y
/

Now, I do not know if for any given row in T1 if it will appear in the output result set 
0, 1 or MANY times (eg: if a given t1.x is joined to every row in t2 by y and there are 2 
rows in t2 with the same value for y -- then that row in t1 will be output 2 times). 

For example, say you have:

T1.X                      T2.Y
------                    --------
1                         1
                          1

That single row T1 in the view will be output 2 times.  If we were to issue:

 update T set x = x+1;

The outcome would be to set X=3, since it appears in there 2 times.  That is not key 
preserved and we will not allow that to happen (the results can be ambigous depending on 
an access plan -- the same statements could result in DIFFERENT data in the database 
depending on how we access the data).

So, if you can assert that a given row in a table will appear at most once in the view -- 
that table is "key preserved" in the view.  It sounds backwards because in our example 
above -- its T2 that needs a unique constraint on Y if we want T1 to be key preserved 
(you put the key on T2, not on T1 to key preserve T1!)

See

http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:273215737113

for more examples....