Key Preserved Table concept


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.

Best Answer

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


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 :


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.