Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I read in the Oracle Documentation about key-preserved table in Updating Join Views Section.

However, I didn't find any simple to way understand it.

I hope to receive some simple conceptual details other than the official Oracle Documentation.

share|improve this question
1  
Have you seen this on AskTom? –  Jack Douglas Dec 5 '11 at 23:10
add comment

2 Answers 2

up vote 6 down vote accepted

Key preserved means that 1 key value goes to 1 table. Giving counter examples may help you understand this concept better.

Example1:

Your view contains aggregation. Suppose you have following view structure.

GroupID, AverageSalary
1 , 10000
2, 12000
3, 14000

In this example: your values comes from more than one rows. If you try to update AverageSalary in this view, database has no way to find WHICH rows to update.

Example2: Your view shows values from more than one table. Your view shows values from PERSON and PERSON_CONTACT_DETAILS(ID,PersonID,ContactType,ContactValue) table.

Example rows :

 1,1,email,ddd@example.com
 1,1,phone,898-98-99

You join this 2 table and show more business friendly information in view.

PersonId,Name,LastName, Phone1,Email1

Here you would like to update Phone1 and Email1. But your personID maps to two different rows, may be more rows, in this example. In this view, again, database has no way to find WHICH rows to update.

Note: If you restrict your view sql and makes it clear to find which rows to update it may work.

This two example is first examples which comes to my mind. They can be increased. But concept is clear. Database needs to map 1 key value to 1 table. For example you have one to one PERSON, PERSON_DETAILS tables. Here view and update will work since it is one to one.

share|improve this answer
add comment

The documentation you have already read says it pretty well. To explain further:

The concept of a key-preserved table is fundamental to understanding the restrictions on modifying join views.

Normally an update acts on a single table. To avoid tortuous subqueries in the filter, Oracle allows you to update a view (or subquery) as long as it is still able to easily map the changes you are making onto real underlying rows in a table. This is possible if the set clause only modifies columns in a 'key preserved' table:

A table is key-preserved if every key of the table can also be a key of the result of the join. So, a key-preserved table has its keys preserved through a join.

For example:

create table foo( foo_id integer primary key, foo_val integer not null );
create table bar( bar_id integer primary key, bar_val integer not null, 
                  foo_id integer not null references foo );

update (select * from foo join bar using(foo_id)) set foo_val=1;
ORA-01779: cannot modify a column which maps to a non key-preserved table

update (select * from foo join bar using(foo_id)) set bar_val=1;
0 rows updated.

the first update fails because Oracle has no way of 1:1 mapping foo_val in the query to foo_val in foo - conversely the second update succeeds because Oracle can 1:1 map each bar_val to bar_val in bar. The important thing is that foo_id is unique in foo - so for each row in bar, there can only be at most one corresponding row in foo (actually exactly 1 in this example, but the same applies for a nullable foreign key - the point is that there is never more than one row).

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.