What is 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.

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).

StackExchange Database Administrators Q#8771, answer score: 9